# MySQL: Optimize UNION with “ORDER BY” in inner queries

I just set up a logging system which consists of multiple tables with the same layout.

There is one table for each data source.

For the log viewer, I want to

• UNION all the log tables,
• filter them by account,
• add a pseudo column for identification of the source,
• sort them by time,
• and limit them for pagination.

All tables contain a field called zeitpunkt that is an indexed date/time column.

My first attempt was:

(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt AS zeit,
'hp' AS source FROM is_log AS l WHERE l.account_id = 730)

UNION

(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt,
'ig' AS source FROM ig_is_log AS l WHERE l.account_id = 730)

ORDER BY zeit DESC LIMIT 10;


The optimizer cannot use the indexes here because all rows from both tables are returned by the subqueries and sorted after the UNION.

My workaround was the following:

(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt AS zeit,
'hp' AS source FROM is_log AS l WHERE l.account_id = 730
ORDER BY l.zeitpunkt DESC LIMIT 10)

UNION

(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt,
'ig' AS source FROM ig_is_log AS l WHERE l.account_id = 730
ORDER BY l.zeitpunkt DESC LIMIT 10)

ORDER BY zeit DESC LIMIT 10;


I was expecting the query engine would use the indexes here since both subqueries should be sorted and limited already prior to the UNION, which then merges and sorts the rows.

I really thought this would be it, but running EXPLAIN on the query tells me the subqueries still search both tables.

EXPLAINing the subqueries themselves shows me the desired optimization but UNIONing them together it does not.

Did I miss something?

I know that ORDER BY clauses inside UNION subqueries are ignored without a LIMIT, but there is a limit.

Edit:
Actually, there probably will also be queries without the account_id condition.

The tables already exist and are filled with data. There might be changes in the layout depending on the source so I want to keep them divided. Additionally, the logging clients use different credentials for a reason.

I have to keep a kind of layer between the log readers and the actual tables.

Here are the execution plans for the whole query and the first subquery as well as the table layout in detail:

https://gist.github.com/ca8fc1093cd95b1c6fc0

• The best index for this would be the compound (account_id, zeitpunkt). Do you have such an index? The second best would be (I think) the single (zeitpunkt) - but the efficiency if that is used depends on how often rows with account_id=730 appear. – ypercubeᵀᴹ Sep 24 '12 at 5:41
• And why UNION DISTINCT? There is no need to force a sort and distinct there, as the results will be different across subqueries, due to the extra, identification column. Use UNION ALL. – ypercubeᵀᴹ Sep 24 '12 at 6:02
• In addition to @ypercube's suggestion, I have a question: wouldn't it be better to have all those logs in the same table, with the addition of the source column? This way you could avoid UNIONs and use index(es) across all your data. – dezso Sep 24 '12 at 7:57
• @ypercube Actually, there probably will also be queries without the account_id condition. The DISTINCT flag is a relict of a previous tries and is actually useless because the results will always differ and because DISTINCT is the dafualt behaviour. The tables already exist and are filled with data. Anyways, there might be changes in the layout depending on the source so I want to keep them divided. Additionally, the logging clients use different credentials for a reason. I have to keep a kind of layer between the log readers and the actual tables. – Lukas Sep 24 '12 at 16:39
• OK, but check if changing to UNION ALL yields different execution plan. – ypercubeᵀᴹ Sep 24 '12 at 16:44

Just out of curiosity, can you try this version? It may trick the optimizer to use the same indices that the subqueries would use separately:

SELECT *
FROM
(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt AS zeit,
'hp' AS source FROM is_log AS l WHERE l.account_id = 730
ORDER BY l.zeitpunkt DESC LIMIT 10)
AS a

UNION ALL

SELECT *
FROM
(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt,
'ig' AS source FROM ig_is_log AS l WHERE l.account_id = 730
ORDER BY l.zeitpunkt DESC LIMIT 10)
AS b

ORDER BY zeit DESC LIMIT 10;


I still think that the best index you could have is the compound (account_id, zeitpunkt). It would yield the 10 rows fast, and no tricks would ne needed.

• Your modification turned out to bring desired results. Thanks! Just as a side note: by now I am not sure which index will be better. I could even use both. I'll have to check how the number of users and the log entries / user will scale. – Lukas Sep 24 '12 at 23:48
• If you are going to need queries with and queries without account_id=?, keep both. – ypercubeᵀᴹ Sep 24 '12 at 23:50
• @ypercube, +1 this is very clever and worked in my (similar) situation too! Can you explain why wrapping the unioned queries in a dummy SELECT * FROM tricks MySQL into using the indexes? – dkamins Jan 3 '13 at 10:01
• @dkamins: The MySQL optimizer is not very clever, usually when there is a derived table like here the (SELECT ...) AS a, it tries to evaluate and optimize the derived table separately from the other derived tables and then the whole query. – ypercubeᵀᴹ Jan 3 '13 at 10:07
• @Lukas, Actually since you do need to ensure that the index is used, using/adding force index will give you a better solution. – Pacerier May 5 '15 at 7:11