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