Details
Major
Description
In join of 4 tables, optimizer won't use covering index with suitable ordering on first table in case join order of last two tables uses index/table scan on one of them instead of eq_ref - that table has only 2 rows, so optimizer does not seem to feel need to use eq_ref on it, but then resulting plan needs filesort. Happens with and without "LIMIT 3".
explain SELECT evaluation, t.ticket_id FROM ticket_game_result g JOIN ticket t ON g.ticket_id=t.ticket_id JOIN uzivatel u ON (t.user_id=u.user_id) JOIN mena c ON c.mena_id=u.mena_id WHERE (g.game_id = 2) AND (g.cancelled = 0) AND (t.vyplacen_date > '2013-06-31 22:00:00') ORDER BY `g`.`evaluation` DESC LIMIT 3;
+------+-------------+-------+--------+------------------------------------------------+---------+---------+---------------------------+------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+------------------------------------------------+---------+---------+---------------------------+------+-------------------------------------------------+ | 1 | SIMPLE | g | ref | PRIMARY,fk__ticket_game_result__ticket_id,eval | PRIMARY | 2 | const | 54 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | t | eq_ref | PRIMARY,FK_ticket_1,vyplacen_date | PRIMARY | 8 | orderby_index.g.ticket_id | 1 | Using where | | 1 | SIMPLE | c | index | PRIMARY | PRIMARY | 2 | NULL | 2 | Using index; Using join buffer (flat, BNL join) | | 1 | SIMPLE | u | eq_ref | PRIMARY,FK_mena | PRIMARY | 4 | orderby_index.t.user_id | 1 | Using where | +------+-------------+-------+--------+------------------------------------------------+---------+---------+---------------------------+------+-------------------------------------------------+
explain SELECT evaluation, t.ticket_id FROM ticket_game_result g IGNORE INDEX (PRIMARY) JOIN ticket t ON g.ticket_id=t.ticket_id JOIN uzivatel u ON (t.user_id=u.user_id) JOIN mena c ON c.mena_id=u.mena_id WHERE (g.game_id = 2) AND (g.cancelled = 0) AND (t.vyplacen_date > '2013-06-31 22:00:00') ORDER BY `g`.`evaluation` DESC LIMIT 3;
+------+-------------+-------+--------+----------------------------------------+---------+---------+---------------------------+------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+----------------------------------------+---------+---------+---------------------------+------+-------------------------------------------------+ | 1 | SIMPLE | g | ref | fk__ticket_game_result__ticket_id,eval | eval | 3 | const,const | 54 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | t | eq_ref | PRIMARY,FK_ticket_1,vyplacen_date | PRIMARY | 8 | orderby_index.g.ticket_id | 1 | Using where | | 1 | SIMPLE | c | index | PRIMARY | PRIMARY | 2 | NULL | 2 | Using index; Using join buffer (flat, BNL join) | | 1 | SIMPLE | u | eq_ref | PRIMARY,FK_mena | PRIMARY | 4 | orderby_index.t.user_id | 1 | Using where | +------+-------------+-------+--------+----------------------------------------+---------+---------+---------------------------+------+-------------------------------------------------+
explain SELECT evaluation, t.ticket_id FROM ticket_game_result g JOIN ticket t ON g.ticket_id=t.ticket_id JOIN uzivatel u ON (t.user_id=u.user_id) STRAIGHT_JOIN mena c ON c.mena_id=u.mena_id WHERE (g.game_id = 2) AND (g.cancelled = 0) AND (t.vyplacen_date > '2013-06-31 22:00:00') ORDER BY `g`.`evaluation` DESC LIMIT 3;
+------+-------------+-------+--------+------------------------------------------------+---------+---------+---------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+------------------------------------------------+---------+---------+---------------------------+------+-------------+ | 1 | SIMPLE | g | ref | PRIMARY,fk__ticket_game_result__ticket_id,eval | eval | 3 | const,const | 54 | Using where | | 1 | SIMPLE | t | eq_ref | PRIMARY,FK_ticket_1,vyplacen_date | PRIMARY | 8 | orderby_index.g.ticket_id | 1 | Using where | | 1 | SIMPLE | u | eq_ref | PRIMARY,FK_mena | PRIMARY | 4 | orderby_index.t.user_id | 1 | | | 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 2 | orderby_index.u.mena_id | 1 | Using index | +------+-------------+-------+--------+------------------------------------------------+---------+---------+---------------------------+------+-------------+
Attached is testing dump of 100KB database showing the behavior - on our production server there was one more trouble with plan changing when otherwise unused index is ignored, but I was not able to replicate that on this smaller dataset and I have seen some similar issue already.
Dump contains analyze table for all 4 tables, because without it after import I was not able to replicate. Plans were totally different.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Did you check the performance impact on your real-life data?
Last time I checked on MySQL was 5.1..
Elena Stepanova
On real data:
those with filesort run around 0.42s after warming up - on first run after some time I have seen 5 secs
the one without filesort (straight_join) are mostly under 0.03s
Assigning to Sergei Petrunia for further consideration.
Note: Looks pretty much the same on MySQL 5.6
MySQL [test]> explain -> SELECT evaluation, t.ticket_id -> FROM ticket_game_result g IGNORE INDEX (PRIMARY) -> JOIN ticket t ON g.ticket_id=t.ticket_id -> JOIN uzivatel u ON (t.user_id=u.user_id) -> JOIN mena c ON c.mena_id=u.mena_id -> WHERE (g.game_id = 2) AND (g.cancelled = 0) AND (t.vyplacen_date > '2013-06-31 22:00:00') -> ORDER BY `g`.`evaluation` DESC LIMIT 3; +----+-------------+-------+--------+----------------------------------------+---------+---------+------------------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+----------------------------------------+---------+---------+------------------+------+----------------------------------------------------+ | 1 | SIMPLE | g | ref | fk__ticket_game_result__ticket_id,eval | eval | 3 | const,const | 54 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | c | index | PRIMARY | PRIMARY | 2 | NULL | 2 | Using index; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | t | eq_ref | PRIMARY,FK_ticket_1,vyplacen_date | PRIMARY | 8 | test.g.ticket_id | 1 | Using where | | 1 | SIMPLE | u | eq_ref | PRIMARY,FK_mena | PRIMARY | 4 | test.t.user_id | 1 | Using where | +----+-------------+-------+--------+----------------------------------------+---------+---------+------------------+------+----------------------------------------------------+ 4 rows in set (0.00 sec) MySQL [test]> explain -> SELECT evaluation, t.ticket_id -> FROM ticket_game_result g -> JOIN ticket t ON g.ticket_id=t.ticket_id -> JOIN uzivatel u ON (t.user_id=u.user_id) -> STRAIGHT_JOIN mena c ON c.mena_id=u.mena_id -> WHERE (g.game_id = 2) AND (g.cancelled = 0) AND (t.vyplacen_date > '2013-06-31 22:00:00') -> ORDER BY `g`.`evaluation` DESC LIMIT 3; +----+-------------+-------+--------+------------------------------------------------+---------+---------+------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+------------------------------------------------+---------+---------+------------------+------+--------------------------+ | 1 | SIMPLE | g | ref | PRIMARY,fk__ticket_game_result__ticket_id,eval | eval | 3 | const,const | 54 | Using where; Using index | | 1 | SIMPLE | t | eq_ref | PRIMARY,FK_ticket_1,vyplacen_date | PRIMARY | 8 | test.g.ticket_id | 1 | Using where | | 1 | SIMPLE | u | eq_ref | PRIMARY,FK_mena | PRIMARY | 4 | test.t.user_id | 1 | NULL | | 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 2 | test.u.mena_id | 1 | Using index | +----+-------------+-------+--------+------------------------------------------------+---------+---------+------------------+------+--------------------------+ 4 rows in set (0.00 sec) MySQL [test]> select @@version; +--------------+ | @@version | +--------------+ | 5.6.24-debug | +--------------+ 1 row in set (0.00 sec)