MySQL Forums
Forum List  »  Performance

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.)

Options: ReplyQuote


Subject
Views
Written By
Posted
3577
March 14, 2013 03:28PM
1209
March 14, 2013 09:08PM
1176
March 14, 2013 10:41PM
Re: Optimizing a query with UNION
1301
March 15, 2013 11:32PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.