ncoo
asked on
MySQL Union with Order By and Limit
If I have two select statements and use union to join them where is the best place to put order by and limit.
For example:
( select * AS alias_names FROM T1 )
UNION
( select * AS alias_names FROM T2 )
ORDER BY alias_id DESC
LIMIT BY 1,11
or would this be better:
( select * AS alias_names FROM T1 ORDER BY T1_id DESC LIMIT BY 1,11)
UNION
( select * AS alias_names FROM T2 ORDER BY T2_id DESC LIMIT BY 1,11)
ORDER BY alias_id DESC
LIMIT BY 1,11
I know alias names can not be used like that.
I'm interested in the performance issues of both queries, which query is faster?
My thoughts are Query 1 is faster up to X records and then Query 2 is faster, but what is X?
I have looked at the MySQL Reference Manual it seems to suggest this, can anyone provide additional information about this and experience with it?
For example:
( select * AS alias_names FROM T1 )
UNION
( select * AS alias_names FROM T2 )
ORDER BY alias_id DESC
LIMIT BY 1,11
or would this be better:
( select * AS alias_names FROM T1 ORDER BY T1_id DESC LIMIT BY 1,11)
UNION
( select * AS alias_names FROM T2 ORDER BY T2_id DESC LIMIT BY 1,11)
ORDER BY alias_id DESC
LIMIT BY 1,11
I know alias names can not be used like that.
I'm interested in the performance issues of both queries, which query is faster?
My thoughts are Query 1 is faster up to X records and then Query 2 is faster, but what is X?
I have looked at the MySQL Reference Manual it seems to suggest this, can anyone provide additional information about this and experience with it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
use UNION ALL ! You wil avoid an unecassary sort :-)
Most important is question about indexes. if you have index on T1_id and T2_id is it ok to use
( select * AS alias_names FROM T1 ORDER BY T1_id DESC LIMIT 11)
UNION ALL
( select * AS alias_names FROM T2 ORDER BY T2_id DESC LIMIT 11)
ORDER BY alias_id DESC
LIMIT 11
Most important is question about indexes. if you have index on T1_id and T2_id is it ok to use
( select * AS alias_names FROM T1 ORDER BY T1_id DESC LIMIT 11)
UNION ALL
( select * AS alias_names FROM T2 ORDER BY T2_id DESC LIMIT 11)
ORDER BY alias_id DESC
LIMIT 11
ASKER
Thank you for the reply but how does UNION ALL differ exactly from standard UNION?
Does it only perform the order by on the selects in union if they're needed.
Also how would I tackle LIMIT 11,20 using query 2, or is it not possible? Would I have to use query 1 for this?
Does it only perform the order by on the selects in union if they're needed.
Also how would I tackle LIMIT 11,20 using query 2, or is it not possible? Would I have to use query 1 for this?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
By performing the limit by 3 times on query 2 the results aren't the same as query 1, anyway around this?