Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Cannot Reproduce
-
5.5.32
-
None
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 |
|
+------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
|
Attachments
Issue Links
- relates to
-
-
- Open
-