Improve company productivity with a Business Account.Sign Up

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

union & order by

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
myyis
Asked:
myyis
1 Solution
 
Steve BinkCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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