[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

MySQL Union with Order By and Limit

Posted on 2009-04-12
5
Medium Priority
?
3,760 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 1600 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 400 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this article, we’ll look at how to deploy ProxySQL.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
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 …
Suggested Courses

650 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