Какой индекс построить в таблице MySQL для максимальной скорости выборки?

Question

[fedot02]

Здравствуйте.

Есть таблица:

CREATE TABLE IF NOT EXISTS `table` (
  `id` int(11) NOT NULL,
  `state` enum('top','middle','bottom') NOT NULL,
  `sub_state` enum('ok','fail') NOT NULL,
  `grade` enum('great','bad') NOT NULL,
  `last_access` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Нужно выбрать максимально быстро запись, которая удовлетворяет условию: state in ('top', 'middle'), sub_state = 'ok', grade = 'great' и last_access был самым минимальным.

Я составил запрос

SELECT * FROM table WHERE state in ('top', 'middle') AND sub_state = 'ok' AND grade = 'great' ORDER BY last_access LIMIT 1

Сделал составной индекс по всем полям, но выборка на количестве записей от 60к идет 50мс, EXPLAIN говорит, что используется filesort, если убрать ORDER BY, то выборка происходит за 0,5мс. Подскажите, пожалуйста, как выбрать нужную запись за минимально возможное время.

Answer 1

[Melkij]

Сделали составной индекс по всем полям в каком порядке этих самых полей?

Самая быстрая выборка - не читать лишние данные вовсе.
Сделайте memory-таблицу, постройте в ней индекс для сортировки и выгружайте в неё записи только удовлетворяющие where. Синхронизировать с основной можно, например, триггерами. Соответственно, в запросе к этой таблице пропускаете where и получаете сортировку по индексу.

Comments

[fedot02]

Индекс в следующем порядке: state, sub_state, grade, last_access

[fedot02]

По поводу memory таблицы - спасибо, сейчас попробую.

Answer 2

[Ололёша Ололоев]

Порядок следования полей в индексе лучше всего определять для данных в вашей таблице.
Если вы хотите построить индекс для оптимизации какого-то определенного запроса, то существует правило, по которому колонки для индекса следует выбирать по selectivity условий в вашем запросе. Т.е. самой первой колонкой должна быть та, where условие для которой фильтрует больше всего записей.

И еще, попробуйте выборку поместить в подзапрос, а order by оставить во внешнем запросе