snek snek - 6 months ago 17
PHP Question

MySQL query runs ok in phpMyAdmin but hangs in PHP

I have a fairly simple query which runs okay when I test it in 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


The row marked with ** changes depending on filtering settings of the application making the query.

Now, when I test this in phpMyAdmin, the query takes a couple of seconds to run (which is fine, since there are thousands of entries and, as far as I know, when using COUNTs and SUMs indexes don't help) and the results are perfect, but when I run the exact same query in PHP (echoed before running), the MySQL thread loads a core to 100% and doesn't stop until I kill it.

If I strip the extra stuff to calculate the COUNT and SUM, the query finishes but the results are useless to me.

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


EXPLAIN after applying indexes and using joins:

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

Answer

Use explicit join instead of implicit

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

Assuming all the joining keys are also primary key of those tables viz:

o.orders_id, c.customers_id, ab.address_book_id 

You will need to add the following indexes if they are not added already

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);

Make sure to take a backup of the tables before applying indexes.