php - Mysql JOIN 查询执行时间太长或超时

Question

我有用于向客户交付元素的数据库。为此，我有 4 个表，delivery_logs、delivery、driver、customer

(我们有送货日志表，因为有时一次送货有许多条目，例如未送货和其他司机取货等)

delivery_log

id delivery_id driver_id  message     date_added
1  1           1         OK         2016-09-13 17:38:15 
2  2           2         OK         2016-09-13 17:35:18 
3  1           1         Not OK     2016-09-13 17:33:10     
4  1           3         OK         2016-09-13 17:32:13 
5  2           4         waiting    2016-09-13 17:20:11 


delivery
delivery_id  customer_id name          status
1            1           delivery 1    done
2            1           delivery 2    done
3            2           delivery 3    done


driver
driver_id  name
1          driver1
2          driver2
3          driver3

customer
customer_id  first_name   last_name
1            name1          other name1
2            name2          other name2
3            name3          other name3
4            name3          other name4

现在我想查询

last 1 record (order by date_added) from delivery_log

for each delivery by each driver

where delivery= done

我想要以下字段

delivery_log.delivery_id  delivery_log.driver_id delivery_log.message date_added 
delivery.name driver.name 
customer.first_name customer.last_name

我正在使用以下查询，其结果很好，但它花费了很长时间且大多数时间超时，因为我的 delivery_log 表几乎有 911065 记录。

SELECT 
    dl.*,
    del.name,
    dr.name,
    c.first_name,
    c.last_name
FROM delivery_log dl
JOIN delivery del 
 ON(dl.delivery_id = del.delivery_id)
JOIN driver dr 
 ON(dr.driver_id = dl.driver_id)
JOIN customer c 
 ON(del.customer_id = c.customer_id)
WHERE del.status IN('DONE','OK')
  AND NOT EXISTS(SELECT 1 FROM delivery_log s
                 WHERE s.delivery_id = dl.delivery_id
                   AND s.driver_id = dl.driver_id
                   AND s.date_added > dl.date_added)

感谢您的帮助。

Answer 1

我认为你应该向delivery_log添加两个索引。一份用于delivery_id，一份用于delivery_id 和driver_id。