MySQL Forums
Forum List  »  Full-Text Search

Re: how this query works?
Posted by: Rick James
Date: September 07, 2011 10:07AM

I believe that in both cases it will evaluate each of the three SELECTs entirely. The outputs will go into a temp table. Then it will the secondary operations: You have, by default UNION DISTINCT, so there is a "DISTINCT" phase. Then there is the outer LIMIT.

What about ORDER BY??? Your queries seem incomplete by saying LIMIT without an ORDER BY. Once you add an ORDER BY, then the choice of which of the 3 (or 45) becomes important.

Also, (even without ORDER BY), what if the first SELECT returned fewer than the LIMIT (1 or 15) rows? Then you need to get into the second and maybe third SELECT.

Note: If you are using this for "pagination" there will two issues:
* Performance will get worse and worse as you go to further "pages".
* For, say, 15 rows per "page", and you want page 10, you need:
Inner query: ... ORDER BY ... LIMIT 150 -- no OFFSET
Outer query: ... ORDER BY ... LIMIT 135, 15 -- similar ORDER BY

50K may not have serious issues, but let's see these to discuss further:
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
How much RAM do you have?

Options: ReplyQuote

Written By
September 04, 2011 11:48AM
Re: how this query works?
September 07, 2011 10:07AM

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.