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

MySQLorder-byperformancequery-performancesubqueryunion

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

Best Answer

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.

Related Solutions

MySql: #1221 – Incorrect usage of UNION and ORDER BY

There are several ways to pass the IN / LIMIT limitation and the UNION / ORDER BY syntax requirements.

Wrap the Union in a derived table:

SELECT * FROM comment AS tbl WHERE sscm_id IN
  ( SELECT sscm_id
    FROM     
      ( ( SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4 
          ORDER BY t1.sscm_id DESC LIMIT 3)
      UNION  ALL
        ( SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3 
          ORDER BY t2.sscm_id DESC LIMIT 3)
      ) AS tmp 
  ) ;

Use JOIN instead of WHERE / IN:
UNION is required here instead of UNION ALL to avoid duplicates

SELECT tbl.* 
FROM comment AS tbl 
    JOIN 
        ( ( SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4 
            ORDER BY t1.sscm_id DESC LIMIT 3)
        UNION 
          ( SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3 
            ORDER BY t2.sscm_id DESC LIMIT 3)
        ) AS tmp 
      ON tmp.sscm_id = tbl.sscm_id ;

Mysql – Optimizing ORDER BY for simple MySQL query

The EXPLAIN SELECT you posted definitely seems counter-intuitive.

If your query included WHERE s.id = ... then the query plan you're seeing might make a little bit more sense, but I'm assuming you're not.

It looks like the optimizer is getting distracted by the facts that supplier is a smaller table and that the supplier_id index in the po table can be used as a covering index... and with those facts in hand, it's overlooking the seemingly-obvious fact that the tables should be read in the opposite order than the one it chooses.

Here are two alternatives.

-- use the STRAIGHT_JOIN directive to insist that the optimizer process the tables in only the listed order:

SELECT STRAIGHT_JOIN * FROM `po` 
INNER JOIN po_suppliers s ON po.supplier_id = s.id
ORDER BY po.id ASC
LIMIT 10;

-- use the FORCE KEY index hint to direct the optimizer to prefer the primary key of the po table:

SELECT * FROM `po` FORCE KEY (PRIMARY) 
INNER JOIN po_suppliers s ON po.supplier_id = s.id
ORDER BY po.id ASC
LIMIT 10;

The first option is probably the better option, since FORCE KEY, in spite of the name, is still only a "hint" that the optimizer can choose to ignore, while STRAIGHT_JOIN genuinely does force the hand of the optimizer to join the tables in the order they're listed.

Best Answer

Related Solutions

MySql: #1221 – Incorrect usage of UNION and ORDER BY

Mysql – Optimizing ORDER BY for simple MySQL query

Related Question