Xericoder Xericoder - 6 months ago 30
MySQL Question

Group by causing using temporary; using filesort

I got an old project to maintain recently (I'm not an experienced developer), and I found an SQL like this:

SELECT
g.goods_id, o.order_status,
SUM(IF(o.shipping_status=0, g.quantity, 0)) AS 'pending',
SUM(IF(o.shipping_status=3, g.quantity, 0)) AS 'picking',
SUM(IF(o.shipping_status=5, g.quantity, 0)) AS 'shipping'
FROM
order_info AS o
LEFT JOIN
order_goods AS g ON g.order_id = o.order_id
WHERE o.order_status in (1,5,6)
GROUP BY g.goods_id


I removed some annoying stuff so it looks much cleaner.
but still, it took me about 10 seconds to get the result.

The database has only 60 thousand rows of records. and it has all the indexes that it needs.

And the explain command shows that the SQL is 'Using temporary; Using filesort'. I think the 'group by' clause caused the problem.

So, I wonder if I can optimize or rewrite it in another way. which will give me the same functionality and both avoid the extra sorting stuff.

Any help would be appreciate and sorry for my bad English.

Indexes for order_goods:

order_id, goods_id...


Indexes for order_info:

order_id, order_status, shipping_status...

The explain output are here:

enter image description here

Answer

i would suggest to change

WHERE o.order_status in (1,5,6)

for

WHERE (o.order_status = 1 OR o.order_status = 5 OR o.order_status = 6)

Should't change to much... but i know that in is quite slow in some mysql version , depends also of the records you have in both tables.

You also could change the if from the select sentence and instead of doing a if in each row you could use it in a where getting just the rows with the status 0,3,5 and adding a group by shipping status.

AND (o.shipping_status = 0 OR o.order_status = 3 OR o.order_status = 5)

All sentence in a row

SELECT
g.goods_id, o.order_status,
SUM(g.quantity) AS 'quantity'
FROM
order_info AS o
LEFT JOIN
order_goods AS g ON g.order_id = o.order_id
WHERE (o.order_status = 1 OR o.order_status = 5 OR o.order_status = 6) AND 
(o.shipping_status = 0 OR o.shipping_status = 3 OR o.shipping_status = 5)
GROUP BY g.goods_id,o.shipping_status

Take care you will not get te same result as expected, but i think this is the fastes way to get your result and maybe it's not so hard to change a bit of your code.

Try execute this and let's see how times goes ;)