Slow MySQL query with Group by 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?
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?