Optimize query mysql Sorting result

Question

Optimize query mysql Sorting result

Navigation

#1 by (5 votes)

5

As the title says, the query I have is this:

SELECT id, first_date_order, last_date_order, 
                (SELECT o.created_at 
                 FROM orders o 
                 WHERE o.customer_id = c.id
                 ORDER BY o.created_at DESC
                 LIMIT 1) 
                 as last_date_order_real,
                 (SELECT o.created_at 
                 FROM orders o 
                 WHERE o.customer_id = c.id
                 ORDER BY o.created_at ASC
                 LIMIT 1) 
                 as first_date_order_real
FROM customer c
WHERE orders > 0

Basically what it does is, pick up all the customers who have an order, the date of their first order and the date of their last order, and then calculate from the relationship order < - > customer, the actual date of both the first and last orders.

It takes more than 4 minutes to execute, most of the time is spent in the Sorting result state, I guess the query problem is that for each customer (1M approx), you have to make 2 queries, and for each of these queries has to sort the records in ascending and descending order.

mysql sql

asked by Victor Company 07.03.2017 в 21:01

source

1 answer

Correct way to show money convert ListDocument to json

score 5 · Accepted Answer

You do not have to make a subquery for each of the data you want. Just make a LEFT JOIN , and use MAX and MIN :

SELECT  c.id, 
        c.first_date_order, 
        c.last_date_order, 
        MAX(o.created_at) last_date_order_real,
        MIN(o.created_at) first_date_order_real
FROM customer c
LEFT JOIN orders o
    ON c.id = o.customer_id
WHERE c.orders > 0
GROUP BY c.id, 
         c.first_date_order, 
         c.last_date_order;