Индекс по binary - непонятное поведение

[gerasim]

Индекс по binary - непонятное поведение

Столкнулся с непонятным для меня поведением индексов, когда они то используются, то нет.
Таблица (может показаться надуманной, это сокращенный вариант):

CREATE TABLE `tututu` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `id64` binary(4) NOT NULL,
  `id_parent` mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id64` (`id64`),
  KEY `id_parent` (`id_parent`)
) ENGINE=InnoDB  ;

В ней 9 строк данных.

Запрос:

EXPLAIN SELECT t1.id, t1.id64, t2.id AS parent
FROM tututu AS t1
LEFT JOIN tututu AS t2 ON t1.id_parent = t2.id
WHERE t1.id64 IN ('0002','0003')
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | t1    | range  | id64          | id64    | 4       | NULL              |    2 | Using where |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 3       | test.t1.id_parent |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+

тут нормально. Уберем джойн

EXPLAIN SELECT t1.id, t1.id64
FROM tututu AS t1
WHERE t1.id64 IN ('0002','0003')
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | index | id64          | id64 | 4       | NULL |    9 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

уже другой тип связывания, хуже. И почему?

Вернемся к прежнему запросу, но добавим в WHERE еще один цыфирь:

EXPLAIN SELECT t1.id, t1.id64, t2.id AS parent
FROM tututu AS t1
LEFT JOIN tututu AS t2 ON t1.id_parent = t2.id
WHERE t1.id64 IN ('0002','0003','0004')
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | t1    | ALL    | id64          | NULL    | NULL    | NULL              |    9 | Using where |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 3       | test.t1.id_parent |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+

И уже key не используется совсем.

Использование в WHERE вместо '0002' и тп. выражений типа 0x30303032, CONVERT('0002',BINARY) - ничего не дает
USE INDEX тоже ничего не меняет.

Если же делать выборку не по id64, а по id - такой проблемы нет.
Если у таблицы удалить столбец id_parent - проблема исчезает.

Проверял на MySQL версий 5.0.x (других под рукой у меня нет)

Хотелось бы понять причину такого поведения индекса. И как можно заставить нормально работать индекс по binary.

 

[Gas]

9 записей очень мало, базе иногда быстрее считать последовательно всю таблицу, чем делать random seeks. Добавь в таблицу записей, хотя бы до 50 и чтоб при выборке доставалось штук 10, тогда индексы должны всегда использоваться.

уже другой тип связывания, хуже. И почему?

почему хуже, Using where; Using index; очень даже хорошо

 

[gerasim]

Автор оригинала: Gas
9 записей очень мало, базе иногда быстрее считать последовательно всю таблицу, чем делать random seeks. Добавь в таблицу записей, хотя бы до 50 и чтоб при выборке доставалось штук 10, тогда индексы должны всегда использоваться.

Точно. Вставил 100 записей. Индекс нормально работает:

+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | t1    | range  | id64          | id64    | 4       | NULL              |    3 | Using where |
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 3       | test.t1.id_parent |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+

почему хуже, Using where; Using index; очень даже хорошо

Я другое имел в виду. В столбце type значение range меняется на index, а если верить мануалу, такой тип связывания хуже, хуже него только ALL

Спасибо!

 

[Gas]

В столбце type значение range меняется на index, а если верить мануалу, такой тип связывания хуже, хуже него только ALL

Проверил, действительно mysql в этом случае (когда мало записей) считает весь индекс.

Может кому интересно будет. Таблица на 10 записей:

explain select id from vacancy_requests where id < [b]2[/b];
+----+-------------+------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table            | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | vacancy_requests | index | PRIMARY       | PRIMARY | 4       | NULL |   [b]10[/b] | Using where; Using index |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+--------------------------+

flush status;
select id from vacancy_requests where id < 2;
show session status like 'Handler_read_%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 0     |
| Handler_read_next     | [b]10[/b]    |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

explain select id from vacancy_requests where id < [b]3[/b];
+----+-------------+------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table            | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | vacancy_requests | range | PRIMARY       | PRIMARY | 4       | NULL |    [b]3[/b] | Using where; Using index |
+----+-------------+------------------+-------+---------------+---------+---------+------+------+--------------------------+

flush status;
select id from vacancy_requests where id < 3;
show session status like 'Handler_read_%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 0     |
| Handler_read_next     | [b]2[/b]     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

Но это не является проблемой, на мизерных таблицах даже не важно наличие индексов (если они не участвуют в джойнах), а если записей хотя бы несколько десятков то выбирается правильный план.