A composite index on position (date, domain_id, position)
and on userdomainexpression (userdomain_id, expression_id)
would help I think.
The join to userdomain
is not needed.
Assuming foreign keys exist, the join to expression
could be removed as well.
The LEFT
joins are actually (equivalent to) INNER
joins (due to the WHERE
conditions.
You could try to rewrite, using GROUP BY
only in positions
and then join (It might be better or worse, worth a try).
Other possible rewrites, using EXISTS
or a subquery (with DISTINCT
) for the join to userdomainexpression
. This might be useful if there is no unique constraint on userdomainexpression (userdomain_id, expression_id)
.
A suggestion, removing 2 tables from the joins:
SELECT
p.domain_id AS comp,
AVG(IFNULL(p.position, 50)) AS avg,
p.date AS period
FROM
userdomainexpression AS ude
JOIN request r
ON r.expression_id = ude.expression_id
JOIN position p
ON p.request_id = r.id
WHERE
ude.userdomain_id = 4
AND p.domain_id IN (3,8,13,67,6,5,15,17,7,10,9,12,16,1,2,4,11,14)
AND p.date >= '2014-11-01' AND p.date <= '2015-03-23'
GROUP BY
p.date, p.domain_id ;
There are only three serious candidates:
(`created_at`,`sql`,`elapsed_seconds`) -- 1
(`created_at`,`elapsed_seconds`,`sql`) -- 2
(`sql`,`created_at`,`elapsed_seconds`) -- 3
Both are "covering". That is, the query can be handled entirely in the index. EXPLAIN
indicates such by saying Using index
.
Analysis:
(`created_at`,`sql`,`elapsed_seconds`) -- 1
(`created_at`,`elapsed_seconds`,`sql`) -- 2
filter first. But then the rest of the index is not in any useful order. So it sorts to do the GROUP BY
and eventually finds the max. It cannot simply reach for the 'last' entry to get MAX
. I don't think either of these is better than the other of the two.
(`sql`,`created_at`,`elapsed_seconds`) -- 3
might avoid the sort, since the sql
values come one at a time. Also, the Optimizer might be able to jump to the starting point in the index for the desired created_at
(for each sql
). Again, it cannot simply reach for the 'last' entry to get MAX
.
I vote for #3. However, this is an area where there have been optimization improvements. That is, an older version of MySQL may not do, for example, the leapfrogging.
Best Answer
There is no need to define inner query, try this,
Output: