Solved

MySQL Union with Order By and Limit

Posted on 2009-04-12
5
3,699 Views
Last Modified: 2013-12-12
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
Comment
Question by:ncoo
  • 2
  • 2
5 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 400 total points
Comment Utility
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
 
LVL 15

Author Comment

by:ncoo
Comment Utility
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
 
LVL 14

Expert Comment

by:racek
Comment Utility
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
 
LVL 15

Author Comment

by:ncoo
Comment Utility
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
 
LVL 14

Assisted Solution

by:racek
racek earned 100 total points
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now