Skip to content

Instantly share code, notes, and snippets.

@Mashkin
Created September 24, 2012 22:25
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Mashkin/ca8fc1093cd95b1c6fc0 to your computer and use it in GitHub Desktop.
Save Mashkin/ca8fc1093cd95b1c6fc0 to your computer and use it in GitHub Desktop.
Issues with optimizing 'ORDER BY' inside a 'UNION' query
EXPLAIN EXTENDED
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;
+----+-------------+-------+-------+---------------+---------------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | l | index | NULL | idx_zeitpunkt | 8 | NULL | 10 | 17170 | Using where |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+----------+-------------+
1 row in set
DESCRIBE is_log; -- same for 'ig_is_log'
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| account_id | int(10) unsigned | NO | | NULL | |
| vnum | int(10) unsigned | NO | | NULL | |
| count | int(10) unsigned | NO | | 1 | |
| preis | int(10) unsigned | NO | | NULL | |
| zeitpunkt | datetime | NO | MUL | NULL | |
+------------+------------------+------+-----+---------+----------------+
6 rows in set
EXPLAIN EXTENDED (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;
+------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | PRIMARY | l | ALL | NULL | NULL | NULL | NULL | 1717 | 100 | Using where; Using filesort |
| 2 | UNION | l | ALL | NULL | NULL | NULL | NULL | 23 | 100 | Using where; Using filesort |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using filesort |
+------+--------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
3 rows in set
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment