Details
Major
Description
CREATE TABLE `sort_bug` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `data` datetime NOT NULL, `t` smallint(5) unsigned NOT NULL, `id_region` smallint(5) unsigned NOT NULL, `id_miasto` smallint(5) unsigned NOT NULL, `typ_obiektu` smallint(5) unsigned NOT NULL, `o` int(10) unsigned NOT NULL, `c` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `KEY_data` (`data`), KEY `o` (`o`) ) ENGINE=InnoDB AUTO_INCREMENT=745193 DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
With a small amount of rows filesort is used instead of index scan:
explain SELECT * FROM sort_bug ORDER BY data DESC LIMIT 1; +------+-------------+----------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | sort_bug | ALL | NULL | NULL | NULL | NULL | 211 | Using filesort | +------+-------------+----------+------+---------------+------+---------+------+------+----------------+
The same is without LIMIT. Analyze table didn't help in this example.
When LIMIT is small adding a bogus WHERE clause can help:
explain SELECT * FROM sort_bug where id > 0 ORDER BY data DESC LIMIT 1; +------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+ | 1 | SIMPLE | sort_bug | index | PRIMARY | KEY_data | 8 | NULL | 1 | Using where | +------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
In this case both costs read_time and index_scan_time are equal to 1. In test_if_cheaper_ordering() index scan is considered if the cost is strictly lower than read_time. Maybe it would be better to change < to <= ?
— sql/sql_select.cc 2013-08-21 18:27:02 +0000
+++ sql/sql_select.cc 2013-08-22 10:12:40 +0000
@@ -23535,7 +23535,7 @@
index_scan_time= select_limit/rec_per_key *
min(rec_per_key, table->file->scan_time());
if ((ref_key < 0 && (group || table->force_index || is_covering)) ||
- index_scan_time < read_time)
+ index_scan_time <= read_time)
{
ha_rows quick_records= table_records;
if ((is_best_covering && !is_covering) ||
It looks like bug is not reproducible any more on 10.1 (checked with innodb and aria).
We can not reproduce it on 10.1. But if you feel that we are wrong please reopen it.
file with insert data