Не используется индекс при сортировке

[sage]

Есть таблица test, содержащая 100 тыс. записей:

CREATE TABLE IF NOT EXISTS `test` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `position` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `position` (`position`)

Почему при сортировке по `position` не используется индекс?

EXPLAIN SELECT *
FROM `test`
ORDER BY `position` DESC

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	        test 	ALL 	NULL 	        NULL 	NULL 	        NULL 	100000 	Using filesort

 

[fixxxer]

Limit-а нет? Тут mysql, видимо, полагает, что быстрее отсортировать всю таблицу целиком, не трогая индекс вообще, чем задействовать индекс. Для такого числа строк это стремно (может не влезть в sort buffers), но для небольшого оправдано. Впрочем известно что оптимизатор в mysql примитивный.

Тебе действительно не нужен limit? С ним индекс должен использоваться.

 

[fixxxer]

UPDATE:

mysql> EXPLAIN SELECT position, id FROM `test` ORDER BY `position` DESC limit 10;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------+
|  1 | SIMPLE      | test  | index | NULL          | position | 3       | NULL |   10 |       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------+

mysql> EXPLAIN SELECT position, id FROM `test` ORDER BY `position` DESC limit 1000;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL | 9999 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

Что-то я перестал понимать логику. Тут полагается, что при определенной селективности дешевле вообще не трогать индекс? хм.

 

[sage]

спасибо. да, с limit индекс используется. иду издалека, пытаясь понять, почему не используется индекс при следующей ситуации.

CREATE TABLE IF NOT EXISTS `catalog_sorted_by_added` (
  `itemid` mediumint(8) unsigned NOT NULL,
  `position` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`itemid`,`position`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

CREATE TABLE IF NOT EXISTS `catalog_categories` (
  `catid` smallint(5) unsigned NOT NULL,
  `itemid` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`catid`,`itemid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

запрос

EXPLAIN SELECT `t4`.`itemid`
FROM `catalog_sorted_by_added` `t4` , (
SELECT `t1`.`itemid`
FROM `catalog_categories` `t1`
WHERE `t1`.`catid` IN ( 2, 3, 4, 5 )
GROUP BY `t1`.`itemid`
ORDER BY NULL
) `t3`
WHERE `t4`.`itemid` = `t3`.`itemid`
ORDER BY `t4`.`position`
LIMIT 0 , 40

id 	select_type 	table 	        type 	possible_keys 	key 	key_len 	ref 	  rows 	   Extra
1 	PRIMARY 	<derived2> 	ALL 	NULL 	        NULL 	NULL 	        NULL 	  1722 	   Using temporary; Using filesort
1 	PRIMARY 	t4 	        ref 	PRIMARY 	PRIMARY 	3 	t3.itemid 1        Using index
2 	DERIVED 	t1 	        index 	PRIMARY 	PRIMARY 	5 	NULL 	  1999 	   Using where; Using index; Using temporary

 

[prolis]

Если ты про это подзапрос:

SELECT `t1`.`itemid`
FROM `catalog_categories` `t1`
WHERE `t1`.`catid` IN ( 2, 3, 4, 5 )
GROUP BY `t1`.`itemid`
ORDER BY NULL

то ORDER BY можно убрать и catid вынести из праймари кея, он селективности не добавляет

 

[sage]

то ORDER BY можно убрать

уберём - получим filesort

catid вынести из праймари кея

как же его можно вынести, если по нему идёт выборка?

вообще я имел ввиду запрос SELECT `t4`.`itemid`. Как видно из explain, для сортировки и лимита не используется индекс по `position`.

 

[prolis]

уберём - получим filesort
Как видно из explain, для сортировки и лимита не используется индекс по `position`.

Всё используется:
1 PRIMARY t4 ref PRIMARY PRIMARY

а вот над вышеуказанным запросом надо поработать, если там много записей

 

[sage]

используется для нахождения записи, но не для сортировки

1 	PRIMARY 	<derived2> 	ALL 	NULL 	        NULL 	NULL 	        NULL 	  1722 	   Using temporary; Using filesort

 

[sage]

достаточно взглянуть на пример fixxxer'а, чтобы понять, что оптимизатор у mysql достаточно странный)

 

[fixxxer]

Я уже привык force index расставлять

 

[prolis]

Секундочку:
1. derived2 - это внутренний подзапрос к catalog_categories, никакой сортировки там нет (кроме вырожденной by null)
2. насколько мне известно, mysql не умеет показывать в експлейне использование индексов к подзапросам (поскольку по сути это подзапрос к временной таблице, где индексов и нет)
3. поведение оптимизатора при неиспользовании индекса при сортировке всей таблицы я не нахожу странным. Например:

EXPLAIN SELECT `position`
FROM `test`
ORDER BY `position` DESC

(я заменил * на поле в индексе) по крайней мере в Оракле приведет к другому результату
4. несложный запрос, можно развернуть и без подзапросов