Slow MySQL query with Group by

Question

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?

• Md Haidar Ali Khan: Try to use "Where" Clause in the Query.
• jkavalik: Your query has to count everything to just throw away all but the first row. Add a condition to select only rows for the minimal orders.id and remove the limit.
• jkavalik: And you seem to be missing an index on order_id in tickets. That might make the query faster 100-1000 times by itself.
• Vérace: SHOW CREATE TABLE orders\G; (and for tickets).
• Rick James: What is the relationship between orders and tickets? There seem to be a lot more orders than tickets, yet you are counting the number of tickets for one order???

Answer 1

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?