Cелективность в составном индексе

Привет.

Решил вот проверить, есть ли разница между размерами таблиц с одинаковыми данными, но по разному раположенными столбцами в составном индексе, а заодно и скорость выборки.

По теории левее желательно ставить более селективный столбец, но, как-то разницы вот не заметил.

Результат: скорость одинаковая, размер таблиц одинаковый.

Просто решил выложить, если кому любопытно.

Итак, 2 таблицы INNODB, по 100 000 строк в каждой.

Test1 и Test2



У первой составной индекс:a/b/c/d
У второй составной индекс:d/a/b/c

Запросы вида:

SELECT `name` 
FROM `test1` 
WHERE `a`='1'
AND `b`='0'
AND `c`='1'
AND `d` = '112915'

и

SELECT `name` 
FROM `test2` 
WHERE `d`='112915'
AND `a`='1'
AND `b`='0'
AND `c`='1'

Показали одинаковую скорость.
На диске таблицы и их индексы занимают одинаковое место.

Может есть какие-то комментарии? :)

MiksIr

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

А выборка только по `a` как?

ABC Забудь про ENUM как за страшный сон. Бесполезный тип данных.
Лучше использовать вначале столбцы с большей селективностью, чтоб не перегребать лишние данные в памяти(хоть это выполняется и очень быстро).

Да причём тут селективность-шмелективность?
EXPLAIN SELECT WHERE `a`=?

Я подобную хрень проверял в Постгре, с таблицами большого размера. И однозначно могу сказать, что от порядка колонок в индексе скорость зависит. Причем заметно. Только надо "правильно проверять" , в т.ч. исходные данные должны быть подходящие.
Уточняю насчет "подходящести". Если у тебя разных вариаций в каждой колонке примерно одинаковое количество, то порядок будет не важен. Но если у тебя в колонке "a" всего 100 разных значений, в колонке "b" 1000, а в колонке "c" - 10000, причем всего в таблице 2-3 млн записей, то тут уже порядок будет важен.

PS. По-моему, я тут даже тему делал об этом... Только было сие несколько лет тому назад. Сейчас искать лень, если честно

Это сообщение отредактировал sergeiss - 28.01.2014 - 20:56

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

Valick Ты меня пугаешь

И меня ты тоже пугаешь такими высказываниями
Это говорит человек, которого я считаю спецом в веб-программировании вообще и в работе с БД в частности - или это под твоим аккаунтом кто-то другой пишет?

sergeiss, может я не внимательно тему прочитал

Есть такое понятие как "мощность"(Сardinality) индекса, который зависит от селективности.
Так вот, в том же Mysql, оптимизатор проверяет эффективность индексов и если в случае когда есть композитный индекс field1 + field2 и он используется, то при field2 + field1 Mysql может вообще отказаться использовать индекс предпочтя FULL TABLE SCAN.

Нууу...Если звезды зажигаются, значит это кому-нибудь нужно? Пруф о бесполезности работы проектировщиков MySQL можно?

Так и делаю:

Посмотрите внимательно на оба запроса.

Составной индекс на 4 колонны, по всем идет выборка.

Вот ради этого я пост и создал. Хотя мой тест с таблицами по 100 тыс. этого не подтвердил.

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

Если первый столбец поле d - varchar - с очень высокой селективностью. В моем случае 3 строчки на 100 тысяч. Тогда для второго столбца всего 3 строки на обработку, а для последующих вообще единственная. Т.е. материал с которым придется работать MySQL совсем мал, всего 3 строчки изначально.

Если первый столбец поле a - ENUM - с очень низкой селективностью. В моем случае ~50000 строк на 100 тысяч. Тогда вплоть до самого последнего столбца будет "ворочаться" тысячи строк.

Больший объем будет занят в оперативной памяти.

Прочитав эту замечательную статью: http://habrahabr.ru/post/141767/

подумал о степени заполнения страниц, ну и решил просто набросать тест, чтоб посмотреть что будет.


Спасибо всем, кто прокомментировал.

это еще почему?