MYSQL индексирование. Как парвильно составить индексы?

Question

[XXX-prog]

К примеру есть таблица, id, name, last_name, age, growth, weight. Допустим она разрастается до 3 млрд. записей. Понятно, что надо индексировать. Вопрос только как правильно учитывая, что поиск может быть как по нескольким колонкам так и по 1. Пишут, что если сделать составной то поиск будет быстрее, но как я предугадаю все варианты?
Я имею ввиду, что искать я могу так:

name, last_name, age, growth, weight
name, last_name, age, growth
name, last_name, age
name, last_name
name...

Вариантов масса

Answer 1

[sim3x]

Неа
Вариантов ровно столько сколько видов запросов делается к таблице

Comments

[XXX-prog]

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

[sim3x]

XXX-prog,
не давать пользователю выбирать
собирать статистику и оптимизировать исходя из нее
кешировать

Answer 2

[Виктор Янышев]

Пишут, что если сделать составной то поиск будет быстрее

Составной нужен когда запрос с более чем 1 колонкой.

но как я предугадаю все варианты?

Не надо пред угадывать. Смотри какие запросы у тебя есть по таблице. Главное смотри что бы для запроса индекс работал полностью, если полностью не будет работать значит он не подходит.

Я имею ввиду, что искать я могу так:

name, last_name, age, growth, weight
name, last_name, age, growth
name, last_name, age
name, last_name
name...

Ты про SELECT или WHERE, ORDERи тд?

Comments

[XXX-prog]

SELECT * FROM TABLE_NAME WHERE name = 'Вася' and last_name = 'Иванов'
SELECT * FROM TABLE_NAME WHERE name = 'Вася'
SELECT * FROM TABLE_NAME WHERE name = 'Вася' and last_name = 'Иванов'
SELECT * FROM TABLE_NAME WHERE name = 'Вася' and last_name = 'Иванов' and age = 28
etc...

[Виктор Янышев]

1. name
   
2. name, last_name
   
3. name, last_name, age
   

Вот из 4 запросов 3 индекса. Порядок имеет значение

[Rsa97]

Виктор Янышев, В данном случае эти три индекса можно объединить в один, (`name`, `last_name`, `age`)

[Виктор Янышев]

Rsa97, если запрос имя = Миша, то он будет использовать только часть составного индекса, это нормально?

[Rsa97]

Виктор Янышев, Нормально, поскольку это непосредственное начало индекса. Запросы, не включающие `name` не смогут использовать такой составной индекс.

Answer 3

[Rsa97]

Тут всё зависит от ваших конкретных поисковых запросов. Если поиск идёт по точному совпадению (`name` = 'value') или по началу строки (`name` LIKE 'value%'), то индекс будет использоваться, если по функции (lower(`name`) = 'value') или по подстроке (name LIKE '%value%'), то не будет.

Составной индекс используется только начиная с первых полей. То есть, если вы сделаете индекс (`name`, `last_name`, `age`, `growth`, `weight`), то при поиске по `name` и `last_name` он будет использоваться, а при поиске только по `last_name` - не будет.

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

Comments

[XXX-prog]

а если поиск будет по `last_name` и `age`, при индексе (`name`, `last_name`, `age`, `growth`, `weight`), он будет использоваться?

[Дмитрий Телепнев]

XXX-prog, нет

[Rsa97]

XXX-prog, Нет. Индекс используется строго по порядку входящих в него полей.

Answer 4

[Andrey Shatokhin]

Вам бы вместо mysql использовать что-то типа elasticsearch