Странная ситуация с SQL запросами MariaDB / PHP

Хоть на результат это никак и не влияет, но план выполнения становится другой. Оптимизатор не отличается умом и сообразительностью.

С LIMIT 1 можно сделать так:

SELECT SQL_NO_CACHE SQL_BIG_RESULT COUNT(*) FROM tbl WHERE user_name="admin" GROUP BY user_id LIMIT 1;

или

SELECT SQL_NO_CACHE COUNT(*) FROM tbl IGNORE INDEX FOR GROUP BY (user_id) WHERE user_name="admin" GROUP BY user_id LIMIT 1;

Что приведет к одному и тому же плану выполнения запроса, как и без LIMIT 1:

  EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM voc_quiz_top WHERE user_name="admin" GROUP BY user_id;
+------+-------------+--------------+------+---------------+------+---------+------+--------+----------------------------------------------+
| id   | select_type | table        | type | possible_keys | key  | key_len | ref  | rows   | Extra                                        |
+------+-------------+--------------+------+---------------+------+---------+------+--------+----------------------------------------------+
|    1 | SIMPLE      | voc_quiz_top | ALL  | NULL          | NULL | NULL    | NULL | 540416 | Using where; Using temporary; Using filesort |
+------+-------------+--------------+------+---------------+------+---------+------+--------+----------------------------------------------+

Все проблема в cardinality. 745 - это очень низко, именно поэтому при использовании индекса user_id (базовый запрос с LIMIT 1) и проседает в производительности, так как использование индекса тут не оправдано.

Самый быстрый вариант без filesort и временных таблиц, притом используя индекс, у меня получился с композитным ключом user_name_user_id:

EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM tbl WHERE user_name="admin" GROUP BY user_id;
+------+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+
| id   | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra                    |
+------+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+
|    1 | SIMPLE      | tbl   | ref  | user_name_user_id | user_name_user_id | 257     | const |    2 | Using where; Using index |
+------+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+

Более того, что на PHP, что через CLI, все запросы выполняются одинаково, EXPLAIN у него тоже одинаковый. Но я проверял не через phpMyAdmin, возможно phpMyAdmin устанавливает какие-то свои переменные после коннекта.

danforth, одно мне точно понятно, запрос ужасен и это проблема сайта)

Видимо логика обработки на deb8 mariadb 10.0 всё таки была немного другая или ещё что-то повлияло - ведь тормоза ранее не ощущались.

Всем спасибо за помощь, наверное дальше уже не стоит мусолить эту тему.

Возможно кому-то интересно.

При оптимизации другой таблицы, которая значительно жирнее, выяснилось что mariadb очень плохо работает с MyIsam, никакие методы оптимизации, индексы и прочее не помогали, помогла только конвертация в Innodb, которая сразу дала прирос по всем тяжёлым запросам примерно на 50-70%. Не смотря на то что разные бенчмарки (возможно старые) говорят что MyIsam на выборку значительно быстрее, однако я установил множество тормозов как только начинаем использовать сортировки, и никакие индексы тут к сожалению не помогают. Поэтому если у кого то ещё есть MyIsam, советую конвертировать в Innodb :)

Фундаментальная разница, не решаемая оптимизацией запросов и таблиц, между MyIsam и Innodb состоит в том, что у Innodb будет гораздо более медленное добавление строк и несколько более медленные операции, затрагивающие за один раз много строк (селекты и апдейты), также для fixed row таблиц, у Innodb будут гораздо более медленные селекты, в случаях когда нельзя использовать индекс (но это обычно решается правильной оптимизацией).

В реальной жизни Innodb может быть быстрее только на нагруженных сайтах (когда в одну таблицу происходят одновременные чтения и записи), и то, только в тех случаях, когда записи вам нужны transaction safe. В иных случаях Innodb может быть быстрее только в тех случаях, когда администратор БД не понимает что делает, и случайно оптимизировал таблицы для MyIsam и Innodb по-разному.

Еще один минус Innodb - с ним гораздо труднее делать и развертывать бэкапы, особенно больших баз.

А вот в случае неправильной оптимизации - тогда да. Таблицы Innodb трудно запороть так, чтобы они случайно работали "еще медленнее". А MyIsam вполне можно.

В MySQL 5.7 точно такая же ситуация с MyISAM. Одна и та же база может летать на 5.6, но тормозить на 5.7.