MySQL запрос работает нормально в phpMyAdmin, но зависает в переполнении стека
У меня есть довольно простой запрос, который работает нормально, когда я проверяю его в phpMyAdmin:
SELECT
c.customers_id,
c.customers_cid,
c.customers_gender,
c.customers_firstname,
c.customers_lastname,
c.customers_email_address,
c.customers_telephone,
c.customers_date_added,
ab.entry_company,
ab.entry_street_address,
ab.entry_postcode,
ab.entry_city,
COUNT(o.customers_id) AS orders_number,
SUM(ot.value) AS totalvalue,
mb.bonus_points
FROM
orders AS o,
orders_total AS ot,
customers AS c,
address_book AS ab,
module_bonus AS mb
WHERE
c.customers_id = o.customers_id
AND c.customers_default_address_id = ab.address_book_id
AND c.customers_id = mb.customers_id
AND o.orders_id = ot.orders_id
AND ot.class = 'ot_subtotal'
** AND c.customers_gender = 'm' AND c.customers_lastname LIKE 'Famlex'
GROUP BY o.customers_id
Строка, помеченная **, изменяется в зависимости от настроек фильтрации приложения, выполняющего запрос.
Теперь, когда я проверяю это в phpMyAdmin, выполнение запроса занимает пару секунд (это нормально, поскольку есть тысячи записей и, насколько я знаю, при использовании индексов COUNT и SUMs это не помогает) и результаты превосходны, но когда я запускаю точно такой же запрос в PHP (повторяется перед запуском), поток MySQL загружает ядро до 100% и не останавливается, пока я его не убью.
Если я уберу лишний материал для вычисления COUNT и SUM, запрос завершится, но результаты для меня бесполезны.
EXPLAIN:
1 SIMPLE mb ALL NULL NULL NULL NULL 48713 Using temporary; Using filesort
1 SIMPLE ot ALL idx_orders_total_orders_id NULL NULL NULL 811725 Using where
1 SIMPLE o eq_ref PRIMARY PRIMARY 4 db.ot.orders_id 1 Using where
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 db.o.customers_id 1 Using where
1 SIMPLE ab eq_ref PRIMARY PRIMARY 4 db.c.customers_default_address_id 1
ОБЪЯСНИТЕ после применения индексов и использования объединений:
1 SIMPLE c ref PRIMARY,search_str_idx search_str_idx 98 const 1 Using where; Using temporary; Using filesort
1 SIMPLE mb ALL NULL NULL NULL NULL 48713 Using where
1 SIMPLE ab eq_ref PRIMARY PRIMARY 4 db.c.customers_default_address_id 1
1 SIMPLE ot ref idx_orders_total_orders_id,class class 98 const 157004 Using where
1 SIMPLE o eq_ref PRIMARY PRIMARY 4 db.ot.orders_id 1 Using where
Решение
Используйте явное соединение вместо неявного
SELECT
c.customers_id,
c.customers_cid,
c.customers_gender,
c.customers_firstname,
c.customers_lastname,
c.customers_email_address,
c.customers_telephone,
c.customers_date_added,
ab.entry_company,
ab.entry_street_address,
ab.entry_postcode,
ab.entry_city,
COUNT(o.customers_id) AS orders_number,
SUM(ot.value) AS totalvalue,
mb.bonus_points
FROM
orders o
join orders_total ot on o.orders_id = ot.orders_id
join customers c on c.customers_id = o.customers_id
join address_book ab on c.customers_default_address_id = ab.address_book_id
join module_bonus mb on c.customers_id = mb.customers_id
where
ot.class = 'ot_subtotal'
c.customers_gender = 'm'
AND c.customers_lastname = 'Famlex'
GROUP BY o.customers_id
Предполагая, что все соединительные ключи также являются первичными ключами этих таблиц, а именно:
o.orders_id, c.customers_id, ab.address_book_id
Вам нужно будет добавить следующие индексы, если они еще не добавлены
alter table orders add index customers_id_idx(customers_id);
alter table module_bonus add index customers_id_idx(customers_id);
alter table orders_total add index orders_id_idx(orders_id);
alter table orders_total add index orders_class_idx(class);
alter table customers add index search_str_idx(customers_gender,customers_lastname);
Обязательно сделайте резервную копию таблиц перед применением индексов.
Другие решения
Можете ли вы поделиться дампом SQL вашей записи, чтобы я мог посмотреть?
phpMyAdmin автоматически добавляет предложение limit к Выбрать запросы, поэтому я думаю, что у вас сложилось впечатление, что в phpMyAdmin запрос выполняется нормально, но не через ваш PHP-скрипт.
Попытайтесь добавить явное условие ограничения к запросу, скажем, предел 0, 1000 в phpMyAdmin перед запуском, и посмотрите, не замедляет ли это производительность phpMyAdmin.