Есть большая таблица со статистикой. В ней 3 ключевых поля:
user_id,
action,
created_at.
Так как user_id самое "отличающееся" поле, то индекс создан вот такой:
user_id_action_created_at.
Большинство запросов для к этой таблице следующего вида:
select count(id) as cnt, user_id
from statistics
where user_id = 2 and action = 'click' and created_at > '2019-01-01'
group by user_id
order by cnt desc
И в таком запросе используется индекс целиком.
Но есть еще общая страница со статистикой, которая собирает всю статистику, без конкретного пользователя:
select count(id) as cnt, user_id
from statistics
where action = 'click' and created_at > '2019-01-01'
group by user_id
order by cnt desc
В этом случае в условии отсутствует поле user_id, а так как индекс начинается с этого поля, то, как я понимаю, индекс не используется.
Вот что мне показывает explain:
key_len 835
ref NULL
rows 15432533
filtered 3.33
Extra Using where; Using temporary; Using filesort
Если следовать логике, что без user_id индекс не работает, то почему бы мне просто не добавить поле user_id в условие, но сделать его любым. Это поле всегда больше нуля, поэтому выглядит вот так:
select count(id) as cnt, user_id
from statistics
where user_id > 0 and action = 'click' and created_at > '2019-01-01'
group by user_id order by cnt desc
Explain этого запроса вот такой:
key_len 767
ref NULL
rows 7716426
filtered 3.33
Extra Using index condition; Using where; Using temporary; Using filesort
Поле rows уменьшилось в два раза, добавилось using index в поле extra, а остальное осталось без изменений. Сам запрос без explain выполняется в три раза быстрее.
Действительно ли это работает? Почему MySQL сам не делает такую оптимизацию?
И действительно ли в запросе без user_id мой индекс не используется?
