Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3773
  • Last Modified:

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?
0
ncoo
Asked:
ncoo
  • 2
  • 2
2 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Query 1:

1. Takes all records from T1 and then from T2 and Does UNION operation to join both results and then Limit the output to 11 records.

Query 2:

1. Takes only 11 records from T1 and 11 Records from T2 and does UNION operation on the results and then Finally Limit the Result to 11 records.

Based on the cost wise, Second query will perform better * if you have huge no of records in the tables T1 and T2. Otherwise both will give similar performance.

* -- It depends upon the Index on the alias_id column too.
0
 
ncooAuthor Commented:
Thank you, that is what I was thinking, however the next problem comes when I do LIMIT BY 10, 21.

By performing the limit by 3 times on query 2 the results aren't the same as query 1, anyway around this?
0
 
racekCommented:
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
0
 
ncooAuthor Commented:
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?
0
 
racekCommented:
To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one. The following example uses both clauses:

(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;

read more at: http://dev.mysql.com/doc/refman/5.1/en/union.html
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now