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

123 4
D
На сайте с 05.06.2007
Offline
155
3401

Приветствую!

Пишу на форум так как поисковики мне не помогли. Имеем табличку 25мб с 500к строк, и SQL запрос:

SELECT SQL_NO_CACHE count(*) FROM table WHERE user_name="admin" GROUP BY user_id

Так вот, если выполнять его через phpMyadmin или через свой скрипт с mysqli_query, то время выполнения почти 2 секунды, а если выполнять в консоли через клиент базы mysql, то время 0.1сек. Вопрос не в том как улучшить этот запрос, а в том - почему через PHP этот запрос выполняется долго. Пробовал разные версии php 7.0, 5.6, 5.3, а также разные версии MariaDB 10.0 / 10.1. Пробовал разные настройки и оптимизации в my.cnf, никаких улучшений. Однако если убрать часть условия, например это WHERE user_name="admin" или убрать это GROUP BY user_id, то выполнение через PHP становится таким же быстрым, 0.1 сек. Что за чертовщина, как вычислить причину? :(

Написал не мало шедевров ;)
LEOnidUKG
На сайте с 25.11.2006
Offline
1578
#1

У вас индексы проставлены в user_name и в user_id?

Также COUNT(*) в InnoDB всегда выполняется долговато.

---------- Добавлено 17.04.2018 в 16:02 ----------

Также зачем вам SQL_NO_CACHE? Чисто для теста? В реальности, пусть закеширует и всё.

✅ Трастовых площадок под размещение статей и ссылок. Опыт 12 лет! ( https://searchengines.guru/ru/forum/675690 ) ⭐ Купить вечные трастовые ссылки для сайта ( https://getmanylinks.ru/?srh ) ⭐ Ускорение ваших сайтов (WP, Opencart и др.) + Настройка сервера ( https://searchengines.guru/ru/forum/997205 )
D
На сайте с 05.06.2007
Offline
155
#2

Таблица myisam, индекс есть только на user_id, и это не моя таблица, а из одной CMS, т.е. да, можно придумать более оптимизированный запрос, но мой вопрос именно в том, почему тормозит только через PHP. Напрямую через mysql клиент запрос выполняется очень быстро.

LEOnidUKG
На сайте с 25.11.2006
Offline
1578
#3
Dimanych:
Таблица myisam, индекс есть только на user_id, и это не моя таблица, а из одной CMS, т.е. да, можно придумать более оптимизированный запрос, но мой вопрос именно в том, почему тормозит только через PHP. Напрямую через mysql клиент запрос выполняется очень быстро.

Ничего у вас быстро не выполняется. Вы не учитываете накладные расходы.

Если у вас myisam, тогда у вас проблема в индексах.

user_name обязательно установите индекс и скорость возрастёт в разы.

---------- Добавлено 17.04.2018 в 16:13 ----------

Также смотрите параметры:

max_heap_table_size - Used with GROUP BY

sort_buffer Used with - ORDER BY and GROUP BY

D
На сайте с 05.06.2007
Offline
155
#4

Параметры эти увеличивал, хоть 1гб выставляй, на время не влияет.

Вот в консоли результат запроса вылетает мгновенно, и это точно не кеш, переменные разные пробовал:

MariaDB [(none)]> SELECT SQL_NO_CACHE count(*) FROM table WHERE user_name="admin" GROUP BY user_id;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.11 sec)

Тоже самое в phpMyAdmin:

Отображение строк 0 - 0 (1 всего, Запрос занял 1.7214 сек.)
SELECT SQL_NO_CACHE count(*) AS cnt FROM voc_quiz_top WHERE user_name="admin" GROUP BY user_id

Ещё раз повторюсь, это не мой сайт, а клиентский и оптимизировать SQL я не могу.

Больше похоже на проблему PHP + MariaDB, давно на MySQL с debian 8 таких проблем не замечали. Почему и пишу в администрирование.

Сейчас: Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1

iHead
На сайте с 25.04.2008
Offline
137
#5

Включите в phpMyAdmin профилирование запросов, там будет видно, какая стадия запроса выполняется долго.

Рекомендуемый хостинг партнер 1С-Битрикс (https://www.ihead.ru/bitrix/), PHP-хостинг (https://www.ihead.ru/php/), доверенный партнер RU-CENTER (https://www.ihead.ru/news/573.html), официальный представитель REG.RU в Кирове (https://www.ihead.ru/news/851.html)
LEOnidUKG
На сайте с 25.11.2006
Offline
1578
#6
iHead:
Включите в phpMyAdmin профилирование запросов, там будет видно, какая стадия запроса выполняется долго.

А какая разница, если ничего менять нельзя в структуре?

edogs software
На сайте с 15.12.2005
Offline
734
#7
Dimanych:
Приветствую!

Пишу на форум так как поисковики мне не помогли. Имеем табличку 25мб с 500к строк, и SQL запрос:
SELECT SQL_NO_CACHE count(*) FROM table WHERE user_name="admin" GROUP BY user_id

Так вот, если выполнять его через phpMyadmin или через свой скрипт с mysqli_query, то время выполнения почти 2 секунды, а если выполнять в консоли через клиент базы mysql, то время 0.1сек. Вопрос не в том как улучшить этот запрос, а в том - почему через PHP этот запрос выполняется долго. Пробовал разные версии php 7.0, 5.6, 5.3, а также разные версии MariaDB 10.0 / 10.1. Пробовал разные настройки и оптимизации в my.cnf, никаких улучшений. Однако если убрать часть условия, например это WHERE user_name="admin" или убрать это GROUP BY user_id, то выполнение через PHP становится таким же быстрым, 0.1 сек. Что за чертовщина, как вычислить причину? :(

А коннект к базе у Вас в обоих случаях одинаковый?

Если в одном случае сокеты, а в другом случае ИП, то разница может упираться именно в это.

Разработка крупных и средних проектов. Можно с криптой. Разумные цены. Хорошее качество. Адекватный подход.
D
На сайте с 05.06.2007
Offline
155
#8

Профилирование показало задержку только в этом:

Array

(

[Status] => Sending data

[Duration] => 1.736824

)

Да, на сайтах как обычно идёт подключение через localhost.

Однако пока не понятно, если возвращаемый результат должен быть всего лишь 1 число, то почему так долго, ощущение что он передаёт всю эту таблицу на 25мб с 500к строк. 😒

LEOnidUKG
На сайте с 25.11.2006
Offline
1578
#9
о, ощущение что он передаёт всю эту таблицу

\

Он её всю перебирает из-за того что нет индекса на поле.

edogs software
На сайте с 15.12.2005
Offline
734
#10
Dimanych:
Да, на сайтах как обычно идёт подключение через localhost.

Именно через localhost, а не через 127.0.0.1

Просто кроме как способом коннекта разницу в скорости одного и того же запроса из разных клиентов объяснить затруднительно.

Еще вариант, может тестовую базу данных создали и позабыли 😂 не, ну а че, бывает. Со скрипта коннектитесь к одной, с консоли к другой.

Можно рассуждать про отсутствие индексов, но вопрос о том почему они вдруг появляются при запросе из консоли работают, а при работе с сайта нет - вряд ли будет отвечен.

123 4

Авторизуйтесь или зарегистрируйтесь, чтобы оставить комментарий