Mysql filesort WHY?

[Redjik]

EXPLAIN SELECT id,name FROM `test` ORDER BY parent,position

Using filesort

EXPLAIN SELECT id FROM `test` ORDER BY parent,position

Using index

name - varchar(255)
индекс на два поля (parent,position)

пробовал FORCE INDEX

гуглю - не нахожу

 

[weregod]

индекс не влез в память, отведенную под индексы?

 

[fixxxer]

индекс на два поля (parent,position)

уверен, что на (parent,position) а не на (position,parent)?

 

[Redjik]

уверен, что на (parent,position) а не на (position,parent)?

да

CREATE TABLE IF NOT EXISTS `test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`parent` int(11) unsigned DEFAULT NULL,
`position` smallint(5) unsigned DEFAULT '1',
PRIMARY KEY (`id`),
KEY `position` (`position`),
KEY `parent` (`parent`),
KEY `order_index` (`parent`,`position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Разница в запросах, наличие в селекте поля name... первый Using filesort, второй Using index

 

[fixxxer]

Запрос прямо такой как есть, без лимита? Ну все тогда правильно он делает, быстрее отсортировать результат, чем елозить из индекса в таблицу для каждого name. Сделай большую таблицу и limit 10, будет использоваться индекс

 

[Redjik]

Логику понимаю, но нет... не то (2к записей)

EXPLAIN SELECT id,name FROM `test` ORDER BY parent,position LIMIT 30


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

 

[fixxxer]

Если только что туда много вгадил, сделай ей analyze table.

 

[Redjik]

в первую очередь делал, ага

 

[fixxxer]

мммм ну видимо все влазит в sort buffers и отсортировать все равно быстрее

конечно в постгресе explain больше информации дает, тут только гадать

 

[Redjik]

мммм ну видимо все влазит в sort buffers и отсортировать все равно быстрее

вот об этом я не подумал, скорее всего так и есть

 

[WMix]

я если чесно не догоняю, в чем может быть причина, но возможно поможет и причину найдешь тут
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

 

[~WR~]

Потому что это InnoDB.
На самом деле, индекс выглядит так: (parent, position, id).

Если выбираем только id, то получаем covering index, и в таблицу идти не надо вовсе. Быстрее прочитать из индекса сразу в нужном порядке.
Если выбираем другие поля, то индекс использовать нельзя, и обязательно надо идти в таблицу. В этом случае действительно быстрее сделать seq scan + сортировку, чем выбирать все ряды по-одному через index scan.

 

[Gas]

[deleted]
не прочитал тему

 

[fixxxer]

~WR~
та не, нормально они используются. Другое дело, что отсортировать быстрее, чем ходить в данные. Но по индексу ходить можно, смотри

CREATE TABLE `a` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `parent` int(11) DEFAULT NULL,
  `position` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent` (`parent`,`position`)
) ENGINE=InnoDB

mysql> explain select id, name from a order by parent,position;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql> explain select id, name from a force index(parent) order by parent,position;
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key    | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------+
|  1 | SIMPLE      | a     | index | NULL          | parent | 10      | NULL |    3 |       |
+----+-------------+-------+-------+---------------+--------+---------+------+------+-------+

 

[~WR~]

Эхехе) Его даже в possible_keys нет. Но force index работает в такой ситуации.
Всё больше понимаю developer'ов постгреса, которые яростно сопротивляются вводу хинтов.

 

[fixxxer]

Ну в каком-то виде они там есть - можно веса костов подкручивать. Ну или всякие enable_seqscan.