Форум пользователей MySQL

LazY · 03.10.2011 03:49:14

SELECT * 

FROM table

ORDER BY A, id

LIMIT 1000

Негодник, даже не думает использовать индекс: possible_keys NULL, Using_filesort.
Cardinality будь здоров, т.к. вторая колонка - первичный ключ.
10^6 записей, т.е. сканировать таблицу ради 1000 записей невыгодно.

Почему бы это?

rgbeast · 03.10.2011 03:58:27

Вообще не использует ключ или только для LIMIT 1000? Приведи SHOW CREATE TABLE и результат EXPLAIN.

LazY · 04.10.2011 01:30:06

SHOW CREATE TABLE items (

    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

    height INT UNSIGNED NOT NULL DEFAULT 0,

    ...

    KEY `height_id_idx`     (height, id)

    ...

)

mysql> explain select SQL_NO_CACHE * from items order by height,id \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: items

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 101198

        Extra: Using filesort

1 row in set (0.00 sec)

Что интересно, помогает FORCE INDEX!

mysql> explain select SQL_NO_CACHE * from items FORCE INDEX (height_id_idx) order by height,id \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: items

         type: index

possible_keys: NULL

          key: height_id_idx

      key_len: 8

          ref: NULL

         rows: 101198

        Extra: 

1 row in set (0.00 sec)

А USE INDEX - нет:

mysql> explain select SQL_NO_CACHE * from items USE INDEX (height_id_idx) order by height,id \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: items

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 101198

        Extra: Using filesort

1 row in set (0.00 sec)

Для запроса с соотв. WHERE индекс тоже не используется:

mysql> explain select * from items where (height>"1") OR (height="1" AND id>24734) order by height ASC,id ASC \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: items

         type: ALL

possible_keys: PRIMARY,height_id_idx

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 101198

        Extra: Using where; Using filesort

1 row in set (0.00 sec)

Зато, похоже, начинает использоваться, когда добавляется LIMIT (хотя непонятно, почему key не NULL и при этом в Extra нет Using index):

mysql> explain select * from items where (height>"1") OR (height="1" AND id>24734) order by height ASC,id ASC LIMIT 1001;

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

| id | select_type | table | type  | possible_keys         | key           | key_len | ref  | rows  | Extra       |

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

|  1 | SIMPLE      | items | range | PRIMARY,height_id_idx | height_id_idx | 8       | NULL | 51229 | Using where |

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

rgbeast · 04.10.2011 20:26:45

Странное поведение. Попробуй этот запрос в MariaDB, они гогворили, что оптимизатор серьезно переделали.

LazY · 04.10.2011 22:38:32

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

В MariaDB не могу. Не у меня дело происходит

rgbeast · 04.10.2011 23:48:16

Или не умеет или думает, что файлсорт лучше почему-то. MariaDB просто для интереса стоит посмотреть

LazY · 06.10.2011 09:57:32

Да, действительно думает, что filesort лучше: если сделать LIMIT 100, а не 1000 - начинает использовать индекс сам.
Косвенно на это также указывал тот факт, что он слушался, когда говорили FORCE INDEX (хотя вообще-то в таких случаях не должен)

deadka · 06.10.2011 10:40:24

Миша, а какой вариант работает быстрее и большая ли разница по скорости получилась от насильственного использования height_id_idx? Я тоже встречал случаи, когда в possible keys ключ не показывался, однако если если прописать force index - то начинал его использовать и даже получалось быстрее .

LazY · 06.10.2011 11:18:20

Вроде с FORCE INDEX побыстрее: при 100 тыс. записей и LIMIT 1000 ускорение где-то на полтора порядка (0.02 с vs 0.60 c без FORCE INDEX); без LIMIT - 0.70 c vs 1.00 c.

rgbeast · 06.10.2011 11:29:16

MariaDB все же интересно проверить на этом примере. Насколько они улучшили оптимизатор и если нет - можно написать Монти.

deadka · 06.10.2011 11:30:50

Ну, можно еще в дополнение к MariaDB ведь на bugs.mysql.com закинуть?.. Или опять будет сказано, что это фича?

rgbeast · 06.10.2011 11:33:10

Да, стоит проверить в последней версии MySQL и написать на bugs. Скорее всего это будет feature request.

LazY · 06.10.2011 15:27:12

Я думаю, скажут, что оптимизатор считает выгодным таблицу читать.

deadka · 06.10.2011 15:40:47

Так если быстрее получается по индексу сканировать, а не читать, то почему же оптимизатор считает, что читать выгоднее?

rgbeast · 06.10.2011 19:56:16

Все равно засабмить рабочий пример в bugs - но надо полный, включая создание и наполнение таблиц (например циклом). А потом надо проверить MariaDB и спросить у Монти его мнение.

LazY · 06.10.2011 19:56:32

Видимо, у него неправильные представления о балансе, когда что выгоднее.
По крайней мере, он не безнадежен, т.к. при определенных LIMIT все-таки использует индекс и слушается FORCE INDEX.

Форум пользователей MySQL

#1 03.10.2011 03:49:14

ORDER BY A, id LIMIT N не использует KEY(A, id) :o

#2 03.10.2011 03:58:27

Re: ORDER BY A, id LIMIT N не использует KEY(A, id) :o

#3 04.10.2011 01:30:06

Re: ORDER BY A, id LIMIT N не использует KEY(A, id) :o

#4 04.10.2011 20:26:45

Re: ORDER BY A, id LIMIT N не использует KEY(A, id) :o

#5 04.10.2011 22:38:32

Re: ORDER BY A, id LIMIT N не использует KEY(A, id) :o

#6 04.10.2011 23:48:16

Re: ORDER BY A, id LIMIT N не использует KEY(A, id) :o

#7 06.10.2011 09:57:32

Re: ORDER BY A, id LIMIT N не использует KEY(A, id) :o

#8 06.10.2011 10:40:24

Re: ORDER BY A, id LIMIT N не использует KEY(A, id) :o

#9 06.10.2011 11:18:20

Re: ORDER BY A, id LIMIT N не использует KEY(A, id) :o

#10 06.10.2011 11:29:16

Re: ORDER BY A, id LIMIT N не использует KEY(A, id) :o

#11 06.10.2011 11:30:50

Re: ORDER BY A, id LIMIT N не использует KEY(A, id) :o

#12 06.10.2011 11:33:10

Re: ORDER BY A, id LIMIT N не использует KEY(A, id) :o

#13 06.10.2011 15:27:12

Re: ORDER BY A, id LIMIT N не использует KEY(A, id) :o

#14 06.10.2011 15:40:47

Re: ORDER BY A, id LIMIT N не использует KEY(A, id) :o

#15 06.10.2011 19:56:16

Re: ORDER BY A, id LIMIT N не использует KEY(A, id) :o

#16 06.10.2011 19:56:32

Re: ORDER BY A, id LIMIT N не использует KEY(A, id) :o

Board footer