Optimizing a group by MySQL query

Question

I have an orders table that holds one item for one user order in a row. So each user can appear on many rows. Each order line has a status - PENDING, APPROVED and so on. I want to count for each user the number of orders per status, that is how many orders are approved, how many are still pending and so on. I came up with the following query:

SELECT 
    u.email, u.id,
    (SELECT count(status) FROM orders WHERE user_id = o.user_id AND status = 'PENDING') pending,
    (SELECT count(status) FROM orders WHERE user_id = o.user_id AND status = 'APPROVED') approved,
    (SELECT count(status) FROM orders WHERE user_id = o.user_id AND status = 'REJECTED') rejected,
    (SELECT count(status) FROM orders WHERE user_id = o.user_id AND status = 'ERROR') error
FROM orders o INNER JOIN users u ON o.user_id = u.id
GROUP BY o.user_id, u.email

The problem is that it is mad slow! I have about 5 000 records in orders table and it makes minutes to execute. Please advice how to optimize it.

score 1 · Accepted Answer · answered Sep 23 '15 at 19:52

1

You can get rid of the sub-queries using conditional aggregation:

SELECT 
    u.email, u.id,
    COUNT(CASE WHEN status = 'PENDING' THEN 1 END) pending,
    COUNT(CASE WHEN status = 'APPROVED' THEN 1 END) approved,
    COUNT(CASE WHEN status = 'REJECTED' THEN 1 END) rejected,
    COUNT(CASE WHEN status = 'ERROR' THEN 1 END) error
FROM orders o INNER JOIN users u ON o.user_id = u.id
GROUP BY o.user_id, u.email

answered Sep 23 '15 at 19:52

Giorgos Betsos

71,379
9
63
98

That is great! Thank you. – Martin Dimitrov Sep 23 '15 at 22:34

score 0 · Answer 2 · answered Sep 23 '15 at 20:00

Actually not really sure you need group by:

SELECT u.email, u.id,o.pending,o.approved,o.rejected,o.error 
FROM users u INNER JOIN 
(
    SELECT
    user_id,
    COUNT(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END) pending,
    COUNT(CASE WHEN status = 'APPROVED' THEN 1 ELSE 0 END) approved,
    COUNT(CASE WHEN status = 'REJECTED' THEN 1 ELSE 0 END) rejected,
    COUNT(CASE WHEN status = 'ERROR' THEN 1 ELSE 0 END) error
    FROM orders
) o
ON u.id = o.user_id

Optimizing a group by MySQL query

2 Answers2