Индексы и EXPLAIN: почему запрос тормозит

Запрос летал на тестовой базе в сто строк. На проде, где строк три миллиона, тот же запрос лёг намертво. Разница не в запросе — в том, есть ли у стола отсортированная картотека или сыщику приходится обходить каждый дом по очереди.

Индекс — заранее отсортированная картотека со ссылками на настоящие строки. По ней ищут адрес не обходом всего города, а прямым переходом.

EXPLAIN QUERY PLAN
SELECT * FROM contacts WHERE badge_id = 'B-4471';
-- SEARCH contacts USING INDEX ... → индекс сработал
-- SCAN contacts → индекс не участвует, обошли всю таблицу

«EXPLAIN не решает дело. Он показывает, обходил ли сыщик каждый дом на улице — или знал, куда идти.»

Когда индекс перестаёт помогать?

Индекс работает, только пока колонку не трогают функцией внутри WHERE. Обёрнутая функция считается заново для каждой строки — индекс тут бесполезен, база обходит всё подряд.

WHERE strftime('%Y', date) = '1983'
-- функция на колонке — full scan, индекс не при делах
WHERE date >= '1983-01-01' AND date < '1984-01-01'
-- диапазон вместо функции — индекс снова живой

То же самое с поиском по обрывку: обрывок в начале строки использует индекс как диапазон, обрывок с ведущим `%` в начале шаблона заставляет обойти всё, потому что неизвестно, с чего искать.

LIKE 'Сапф%'  -- индекс работает, известно с чего начать
LIKE '%апф%'  -- полный обход, начала не видно

«Функция на колонке в WHERE — это не ошибка, которую видно. Это медленный запрос, который выглядит абсолютно правильным.»

Чем мешает звёздочка вместо списка колонок?

И последнее, самое тихое: SELECT * читает больше, чем нужно, — и мешает индексу отдать ответ, не заглядывая в саму таблицу. Список нужных колонок вместо звёздочки — часть плана погони.

На работе так объясняют, почему один запрос летает, а другой такой же на вид ползёт: план погони решает индекс, а не количество строк в запросе.

План погони видно только на большой картотеке. В архиве она большая — посмотрите, как ваш запрос идёт по ней.