Re: Optimizing a query with UNION
Posted by:
Rick James
Date: March 15, 2013 11:32PM
> eliminate either the temporary tables or Filesorts?
It is a common myth that these are evil.
Sure, they take time.
But, sure, they may be necessary.
SELECT ... GROUP BY abc ORDER BY xyz will need to do 1 or two "filesorts".
A "filesort" is a misnomer -- in certain situations it is done entirely in RAM.
You need the sort (ORDER BY replyId) in order to find the first few (LIMIT 10). That sort can _sometimes_ be subsumed into the SELECT -- if there is an appropriate index, and the rest of the query does not get in the way. But it can't be, since it is not even the first table to be accessed.
Notice how it starts with m1, and eventually gets to r -- to subsume the ORDER BY r.replyId, it would have to start with r. But that would be less efficient, since the filtering (WHERE) is on m.
So, you probably have 3 sorts. Still, it is "fast enough", yes?
You could try removing the ORDER BY .. LIMIT 10 from each of the inner queries. That would eliminate 2 filesorts, but would increase the size of the temp tables that are UNIONed together. Then the outer sort (ORDER BY) would be much bigger (not just 20 rows). Still this _might_ be faster. Or it _might_ be faster for some values of {$userId} and slower for others.
> ADD INDEX `member_tid_status_idx` (`tid` ASC, `status` ASC)
> ADD INDEX `member_uid_status_tid_idx` (`uid` ASC, `status` ASC, `tid` ASC)
Any need for the first of these indexes can be handled by the second. Get rid of the first.
> Using index
Good -- it means that the _data_ in the relevant table does need to be touched, only the _index_. (Think of an index as another table.)