1

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?

5
  • Try to use "Where" Clause in the Query. May 15, 2016 at 13:06
  • 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
    May 15, 2016 at 14:09
  • 1
    And you seem to be missing an index on order_id in tickets. That might make the query faster 100-1000 times by itself.
    – jkavalik
    May 15, 2016 at 14:10
  • 1
    SHOW CREATE TABLE orders\G; (and for tickets).
    – Vérace
    May 15, 2016 at 15:42
  • 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???
    – Rick James
    May 26, 2016 at 1:52

1 Answer 1

0

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.