Оптимизация моего запроса mysql для использования индекса для сортировки

У меня есть составной индекс, основанный на 3 столбцах, два из которых ограничены в моем запросе, а третий - в порядке по предложению, но mysql не использует индекс для сортировки.

explain select * from videos where public_private='public' and approved='yes' order by number_of_views desc;

+----+-------------+--------+------+--------------------------------+------+---------+------+---------+-----------------------------+
| id | select_type | table  | type | possible_keys                  | key  | key_len | ref  | rows    | Extra     |
+----+-------------+--------+------+--------------------------------+------+---------+------+---------+-----------------------------+
|  1 | SIMPLE      | videos | ALL  | approved,approved_3,approved_2 | NULL | NULL    | NULL | 1476818 | Using where; Using filesort |
+----+-------------+--------+------+--------------------------------+------+---------+------+---------+-----------------------------+

Структура таблицы следующая:

CREATE TABLE `videos` (
  `indexer` int(9) NOT NULL auto_increment,
  `user_id` int(9) default NULL,
  `public_private` varchar(24) default NULL,
  `approved` varchar(24) default NULL,
  `number_of_views` int(9) default NULL,
  PRIMARY KEY  (`indexer`),
  KEY `approved` (`approved`,`user_id`),
  KEY `approved_3` (`approved`,`public_private`,`indexer`),
  KEY `approved_2` (`approved`,`public_private`,`number_of_views`),
) ENGINE=MyISAM AUTO_INCREMENT=1969091 DEFAULT CHARSET=utf8 |

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


mysql optimization query-optimization indexing
person Muhammad Hasan Khan    schedule 17.07.2009    source источник

Ответы (5)


arrow_upward
14
arrow_downward

Я считаю, что ваш запрос, вероятно, соответствует большому проценту данных в таблице. В таких ситуациях оптимизатор MySQL часто выбирает сканирование таблицы и полностью игнорирует индексы, поскольку на самом деле это быстрее, чем выполнять дополнительные чтения всего индекса и использовать его для выбора данных. В этом случае я предполагаю, что public_private='yes' and approved='yes' соответствует значительной части вашей таблицы. Следовательно, если MySQL пропускает использование индекса из-за этого, то он также недоступен для сортировки.

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

select * from videos FORCE INDEX (approved_2) where public_private='public' and approved='yes' order by number_of_views desc;

Однако я бы провел несколько тестов, чтобы убедиться, что то, что вы получаете, на самом деле быстрее, чем то, что выбрал оптимизатор MySQL. Видимо оптимизатор есть некоторые проблемы с выбором для заказа, так что вы определенно можете попробовать и посмотреть, улучшится ли производительность.

person zombat    schedule 17.07.2009
comment
FORCE INDEX решил проблему. Теперь запрос выполняется намного быстрее, и вы правы в отношении запроса, соответствующего большой части базы данных. - person Muhammad Hasan Khan; 18.07.2009

arrow_upward
1
arrow_downward

Порядок имеет значение в составных ключах. Если вы хотите сортировать только по number_of_views с помощью клавиши approved_2, измените:

KEY `approved_2` (`approved`,`public_private`,`number_of_views`)

to:

KEY `approved_2` (`number_of_views`,`approved`,`public_private`)

Составные ключи в MySQL работают слева направо. В приведенном выше примере ключ, объявленный с использованием имплицитности number_of_views, approved и public_private, создает индексы для:

  • number_of_views
  • number_of_views, approved
  • number_of_views, approved, public_private
person Travis Beale    schedule 17.07.2009
comment
Это решение просто лишит MySQL возможности фильтровать по первым двум полям и сортировать по третьему. - person Quassnoi; 17.07.2009
comment
Он спросил, почему индекс не используется для сортировки. - person Travis Beale; 17.07.2009
comment
Предлагаемый вами индекс можно использовать только для сортировки. Индекс, предложенный @op, может использоваться для фильтрации и для сортировки. Ваш индекс не может использоваться для фильтрации, но может (или не может) использоваться для сортировки. - person Quassnoi; 18.07.2009
comment
Я согласен, что это лучшее решение, я просто пытался ответить на очень конкретный вопрос, который был задан. - person Travis Beale; 18.07.2009

arrow_upward
0
arrow_downward

Это должно работать:

`select * from videos where approved='yes' and public_private='public' order by number_of_views desc;`

Если это не так, просто создайте отдельный индекс для number_of_views.

Это должно сработать.

(mysql в основном следует последовательности слева-направо. Таким образом, ваш индекс для approved, public_private, number_of_views не будет работать, если не используется в этой последовательности. т.е. вы можете использовать все три слева, 2 слева или крайний левый 1. Но он выиграл не сработает, если вы не используете крайний левый, вот в чем идея.)

Использование отдельного индекса автоматически сортирует number_of_views, это может помочь в order by - хотя я в этом уверен.

person satya    schedule 08.03.2011
comment
только добавление индекса number_of_views не решит проблему, поскольку будет считаться тот индекс, который удовлетворяет условию where. В ответе OP следует использовать индекс approved_2, а не индексы approved, public_private и number_of_views по отдельности. - person Nikhil Sahu; 12.01.2017

arrow_upward
-2
arrow_downward

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

person Draemon    schedule 17.07.2009

arrow_upward
-2
arrow_downward

добавьте отдельный индекс в столбец number_of_views и посмотрите, работает ли он тогда.

Если ключ представляет собой комбинацию из 3 столбцов, он будет использовать этот конкретный ключ только тогда, когда он использует все 3 для выполнения операции.

person Virat Kadaru    schedule 17.07.2009
comment
Нет. Правильно: если ключ представляет собой комбинацию из 3 столбцов, он будет использовать этот конкретный ключ, когда он использует первый столбец или комбинацию первых двух столбцов или всех трех столбцов. - person Nikhil Sahu; 12.01.2017