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

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

объяснить выбор * из видео, где public_private = "public" и утверждено = "yes" заказ по number_of_views desc;

+ ---- + ------------- + -------- + ------ + -------------- ------------------ + ------ + --------- + ------ + ------- - + ----------------------------- +
| id | select_type | стол | тип | возможные_ключи | ключ | key_len | ref | строки | Extra |
+ ---- + ------------- + -------- + ------ + -------------- ------------------ + ------ + --------- + ------ + ------- - + ----------------------------- +
| 1 | ПРОСТО | видео | ВСЕ | утвержден, утвержден_3, утвержден_2 | NULL | NULL | NULL | 1476818 | Используя где; Использование сортировки файлов |
+ ---- + ------------- + -------- + ------ + -------------- ------------------ + ------ + --------- + ------ + ------- - + ----------------------------- +

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

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 использовать индекс для сортировки результатов?

17.07.2009 10:22:25
5 ОТВЕТОВ
РЕШЕНИЕ

Я считаю, что ваш запрос, вероятно, соответствует большому проценту данных в таблице. В таких ситуациях оптимизатор 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. По-видимому, у оптимизатора есть некоторые проблемы с выбором элементов для заказа, так что вы определенно можете попробовать и посмотреть, улучшите ли вы производительность.

13
17.07.2009 18:06:30
СИЛОВОЙ ИНДЕКС решил проблему. Теперь запрос выполняется намного быстрее, и вы правы относительно запроса, соответствующего большой части базы данных.
Muhammad Hasan Khan 18.07.2009 11:02:47

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

-2
17.07.2009 10:38:55

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

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

-2
17.07.2009 11:36:09
Нет . Правильно: если ключ представляет собой комбинацию из 3 -х колонок, он будет использовать этот конкретный ключ , когда он использует первый столбец или комбинацию первых двух столбцов или все три колонки
Nikhil Sahu 12.01.2017 09:14:54

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

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

чтобы:

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
1
17.07.2009 18:19:17
Это решение просто лишит MySQLвозможности фильтрации по первым двум полям и сортировки по третьему.
Quassnoi 17.07.2009 18:20:41
Он спросил, почему индекс не использовался для сортировки.
Travis Beale 17.07.2009 18:25:01
Предлагаемый вами индекс можно использовать только для сортировки. Предложенный индекс @opможет быть использован для фильтрации и сортировки. Ваш индекс не может быть использован для фильтрации, но может (или не может) использоваться для сортировки.
Quassnoi 17.07.2009 21:25:25
Я согласен, что это лучшее решение, я просто пытался ответить на очень конкретный вопрос, который был задан.
Travis Beale 17.07.2009 22:08:16

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

`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- в этом я уверен, хотя.

0
14.02.2012 09:49:17
только добавление индекса number_of_viewsне решит проблему, так как будет рассматриваться тот индекс, который удовлетворяет whereусловию. В ответе ФП approved_2следует использовать индекс , а не индексы approved, public_privateпо number_of_viewsотдельности.
Nikhil Sahu 12.01.2017 09:12:12