[#MDEV-4932] query with order by on indexed column is using filesort

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 |

+------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+

[MDEV-4932] query with order by on indexed column is using filesort Created: 2013-08-21 Updated: 2018-09-19 Resolved: 2015-09-07
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	5.5.32
Fix Version/s:	10.1.7