Solved

MySQL Union with Order By and Limit

Posted on 2009-04-12
5
3,729 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 400 total points
ID: 24125128
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
ID: 24125697
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
ID: 24125872
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
ID: 24126169
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
ID: 24126213
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article discusses how to implement server side field validation and display customized error messages to the client.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

732 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