Bug #73136 | Index scan incorrectly selected over ref+sort leading to x1000 worse performance | ||
---|---|---|---|
Submitted: | 27 Jun 2014 16:34 | Modified: | 30 Jun 2014 8:16 |
Reporter: | Morgan Tocker | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.6.12,5.6.19,5.5, 5.5.38, 5.5.20 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[27 Jun 2014 16:34]
Morgan Tocker
[27 Jun 2014 16:34]
Morgan Tocker
Optimizer Trace from 5.6
Attachment: optimizer_trace.txt (text/plain), 16.34 KiB.
[30 Jun 2014 8:16]
MySQL Verification Team
Hello Morgan, Thank you for the report and test case. Verified as described. Thanks, Umesh
[30 Jun 2014 8:19]
MySQL Verification Team
// 5.5.20 +----+--------------------+--------------+-------+---------------+---------+---------+------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+--------------+-------+---------------+---------+---------+------+------+---------------------------------+ | 1 | PRIMARY | u | ALL | NULL | NULL | NULL | NULL | 8192 | Using temporary; Using filesort | | 2 | DEPENDENT SUBQUERY | activity_log | index | user_id | PRIMARY | 4 | NULL | 102 | Using where | +----+--------------------+--------------+-------+---------------+---------+---------+------+------+---------------------------------+ 2 rows in set (0.00 sec) mysql> explain SELECT SQL_CALC_FOUND_ROWS -> u.*, -> (SELECT created_at FROM activity_log FORCE INDEX (user_id) WHERE user_id = u.id ORDER BY id DESC LIMIT 1) AS activity_at -> FROM -> users AS u -> ORDER BY -> activity_at DESC -> LIMIT 0, 5; +----+--------------------+--------------+------+---------------+---------+---------+-----------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+--------------+------+---------------+---------+---------+-----------+------+---------------------------------+ | 1 | PRIMARY | u | ALL | NULL | NULL | NULL | NULL | 8192 | Using temporary; Using filesort | | 2 | DEPENDENT SUBQUERY | activity_log | ref | user_id | user_id | 4 | test.u.id | 1024 | Using where; Using filesort | +----+--------------------+--------------+------+---------------+---------+---------+-----------+------+---------------------------------+ 2 rows in set (0.00 sec)
[30 Jun 2014 8:20]
MySQL Verification Team
Sorry, typo in earlier note. explain plan were from 5.6.20 mysql> select version(); +-------------------------------------------+ | version() | +-------------------------------------------+ | 5.6.20-enterprise-commercial-advanced-log | +-------------------------------------------+ 1 row in set (0.00 sec) +----+--------------------+--------------+-------+---------------+---------+---------+------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+--------------+-------+---------------+---------+---------+------+------+---------------------------------+ | 1 | PRIMARY | u | ALL | NULL | NULL | NULL | NULL | 8192 | Using temporary; Using filesort | | 2 | DEPENDENT SUBQUERY | activity_log | index | user_id | PRIMARY | 4 | NULL | 102 | Using where | +----+--------------------+--------------+-------+---------------+---------+---------+------+------+---------------------------------+ 2 rows in set (0.00 sec) mysql> explain SELECT SQL_CALC_FOUND_ROWS -> u.*, -> (SELECT created_at FROM activity_log FORCE INDEX (user_id) WHERE user_id = u.id ORDER BY id DESC LIMIT 1) AS activity_at -> FROM -> users AS u -> ORDER BY -> activity_at DESC -> LIMIT 0, 5; +----+--------------------+--------------+------+---------------+---------+---------+-----------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+--------------+------+---------------+---------+---------+-----------+------+---------------------------------+ | 1 | PRIMARY | u | ALL | NULL | NULL | NULL | NULL | 8192 | Using temporary; Using filesort | | 2 | DEPENDENT SUBQUERY | activity_log | ref | user_id | user_id | 4 | test.u.id | 1024 | Using where; Using filesort | +----+--------------------+--------------+------+---------------+---------+---------+-----------+------+---------------------------------+ 2 rows in set (0.00 sec)
[20 May 2015 15:16]
Morgan Tocker
This appears to be fixed in 5.8.0 daily build. Optimizer trace shows quite different output (filesort_priority_queue_optimization is used, costs are very different).