Правильно ли я понимаю работу индексов MySQL?

Question

[WebDev]

Есть таблица новостей. В ней есть 2 колонки author_id и created_at.
Мне нужно выбрать новости определенных авторов, опубликованные после определенной даты.

SELECT * FROM `news` WHERE `author_id` IN (1, 2, 3, 4, 5) AND `created_at` > '2016-03-03'

Встает вопрос, как строить индекс author_id + created_at или created_at + author_id.
Правильно ли я понимаю, что первым в индекс лучше ставить то поле, записей по которому меньше?
Например, записей авторов 1, 2, 3, 4, 5 всего в базе 100, а постов, сделанных после 2016-03-03 - 10000.
В этом случае в индексе первым нужно указывать author_id?
И есть индекс будет строиться, например, из 5 полей, то также расставлять их по убыванию?

Answer 1

[shagguboy]

если в запросе есть ВСЕ поля индекса, то порядок полей в индексе не имеет значения. Если есть не все поля то поля которые есть должны быть первыми.

для вашего примера - составной индекс с автором впереди смогут использовать еще запросы только по автору и не смогут запросы только по дате.

Answer 2

[Максим]

Я не эксперт MySQL, но подозреваю, что порядок не имеет никакого значения. Это работа планировщика. В любом случае, вашу версию можно проверить с помощью explain и sql_no_cache.

Answer 3

[Maa-Kut]

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

Answer 4

[ivankomolin]

Как мне помнится, при таком запросе "WHERE `author_id` IN (1, 2, 3, 4, 5) AND `created_at` > '2016-03-03'" лучшим решением будет один простой индекс author_id или created_at
Дело в том, что при поиске по диапазону mysql не сможет полностью использовать составной индекс.

Составной индекс author_id+created_at подойдет например для запроса "WHERE `author_id` IN (1, 2, 3, 4, 5) AND `created_at` = '2016-03-03'" или "WHERE `author_id` IN (1, 2, 3, 4, 5) ORDER BY `created_at`"

А вообще чтобы не теоретизировать используйте EXPLAIN, с помощью него будет видно какой ключ выбирается при запросе, сколько строк обрабатывается и т.д. Это даст точное понимание какой индекс нужно использовать.

Comments

[shagguboy]

>Дело в том, что при поиске по диапазону mysql не сможет полностью >использовать составной индекс.

это не так

[ivankomolin]

Сделал тестовую табличку и проделав EXPLAIN убедился что при условии вида `created_at` > '2016-03-03'(поиск по диапазону) составной индекс используется неполноценно, т.к. количество выбранных строк всегда равно тому количеству строк, которые отобрались в результате сравнения.

[ivankomolin]

Можете привести аргументы в пользу своего утверждения?

[shagguboy]

>количество выбранных строк всегда равно тому количеству строк, которые >отобрались в результате сравнения.

чего?

[ivankomolin]

Оперируя понятиями explain, количество выбранных строк это поле rows. Оно показывает, сколько строк пришлось выбрать чтобы сформировать результат