MySQL Performance – Slow MySQL Query with Group By

group byMySQLperformance

I have a query that is taking a long time to execute minutes and more. The two tables orders and tickets have around 150k records. This is the query:

SELECT 
  orders.id,
  count(distinct tickets.id) as attached_tickets 
from
  orders
  LEFT JOIN tickets ON tickets.order_id = orders.id
GROUP BY
  orders.id  
LIMIT 1;

I have found that by removing the ORDER BY statement it speeds it up to seconds. These are the results from the EXPLAIN 

+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | orders  | NULL       | index | PRIMARY       | PRIMARY | 4       | NULL | 121291 |   100.00 | Using index; Using temporary; Using filesort       |
|  1 | SIMPLE      | tickets | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |   7347 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------------------------+

And yes i have indexes in both tables by id. Is there any way I can speed up my query?

Best Answer

This will be a lot faster, but has flaws:

SELECT  id, 
      ( SELECT  count(*)
            FROM  tickets
            WHERE  order_id = o.id 
      ) as attached_tickets
    FROM  orders AS o
    LIMIT  1;

It will need INDEX(order_id) on tickets.

  • Which order.id do you want? There is no ORDER BY to say which one LIMIT 1 will pick.
  • This may not generalize well. What is your real query?
Related Question