Solved

union & order by

Posted on 2013-11-17
1
403 Views
Last Modified: 2013-11-18
Hi there,
There are 2 SELECT statements joined by UNION.
The ORDER BY comes at the end.
The question is:  Can I  also put an ORDER BY after every SELECT? If I can, does that help the performance?

eg:

SELECT * FROM (SELECT  * FROM TABLE1  ) ORDER BY ID DESC LIMIT 500 ) AS T1
UNION ALL
SELECT * FROM (SELECT  * FROM TABLE2  ) ORDER BY ID  DESC LIMIT 500 ) AS T2
ORDER BY DATETIME DESC
0
Comment
Question by:myyis
1 Comment
 
LVL 50

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 39655363
MySQL 5.5 Reference Manual :: 13.2.9.4. UNION Syntax

Most of that article pertains to the use of ORDER BY with UNION.

As a sidenote, if you want to improve performance, remove the double-SELECT from your statements:
(SELECT * FROM TABLE1 ORDER BY ID DESC LIMIT 500)
UNION ALL
(SELECT * FROM TABLE2 ORDER BY ID DESC LIMIT 500)
ORDER BY DATETIME DESC

Open in new window

To further improve performance, don't "SELECT *" - identify the columns you need and include only those columns in your field list.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
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.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 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

17 Experts available now in Live!

Get 1:1 Help Now