Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Provide possibility to force not using a subquery #14786

Merged
merged 4 commits into from Sep 18, 2019

Conversation

tsteur
Copy link
Member

@tsteur tsteur commented Aug 18, 2019

refs #14761

Avoids generating queries like this:

CREATE TEMPORARY TABLE IF NOT EXISTS logtmpsegmente5bfbc1f135a495c4f5aad6d8047199c (idvisit  BIGINT(10) UNSIGNED NOT NULL) 
            SELECT
                distinct log_inner.idvisit as idvisit
            FROM
                
        (
            
            SELECT
                log_visit.idvisit
            FROM
                log_visit AS log_visit LEFT JOIN log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit
            WHERE
                ( log_visit.visit_last_action_time >= '2019-04-30 14:00:00'
                AND log_visit.visit_last_action_time <= '2019-05-31 13:59:59'
                AND log_visit.idsite IN ('1') )
                AND
                ( ( log_link_visit_action.idaction_url IN (SELECT idaction FROM log_action WHERE ( name LIKE CONCAT('%', '/admin', '%')  AND type = 1 )) ) )
            GROUP BY
                log_visit.idvisit
            ORDER BY
                NULL
        ) AS log_inner
            ORDER BY
                log_inner.idvisit ASC

where we don't need the subquery anymore for create temp table statements anymore as we always do a distinct idvisit anyway.

Also fixes an issue where the bind was wrongly set for roll up reporting

@tsteur tsteur added not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org. Needs Review PRs that need a code review labels Aug 18, 2019
@tsteur tsteur added this to the 3.12.0 milestone Aug 18, 2019
@@ -274,7 +274,7 @@ public function generateQuery($select, $from, $where, $groupBy, $orderBy, $limit
if (stripos($where, 'and ') === 0) {
$where = substr($where, strlen('and '));
}
$bind = array_slice($bind, 3);
$bind = array();
Copy link
Member Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

We can't just remove 3 entries eg when for roll up reporting there are multiple sites specified. Then bind might be actually several idSites plus the start date and end date

@diosmosis diosmosis merged commit 71f491a into 3.x-dev Sep 18, 2019
@diosmosis diosmosis deleted the logquerybuildernojoin branch September 18, 2019 00:53
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Needs Review PRs that need a code review not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org.
Projects
None yet
Development

Successfully merging this pull request may close these issues.

None yet

2 participants