如何优化3个内连接mysql查询？

Question

为了得到我想要的结果，我需要执行一个3表连接。查询已经花费了20秒来执行，这对于我的应用程序来说是不可接受的。

SELECT DISTINCT test.ORDER.PLANT_NAME FROM test.ORDER
INNER JOIN test.LOAD
    ON test.ORDER.ORDER_CODE = test.LOAD.ORDER_CODE 
    AND test.ORDER.ORDER_DATE = test.LOAD.ORDER_DATE
    AND test.ORDER.PROD_CODE = test.LOAD.PROD_CODE
    AND test.ORDER.REMOVE_RSN_CODE = test.LOAD.REMOVE_RSN_CODE
INNER JOIN test.TRUCK
    ON test.TRUCK.truck_code = test.LOAD.TRUCK_CODE
    AND test.TRUCK.hler_code = "4000584"
ORDER BY PLANT_NAME ASC;

下面是解释：

 '1','SIMPLE','ORDER','ALL',NULL,NULL,NULL,NULL,'4511','Using temporary; Using filesort'
 '1','SIMPLE','TRUCK','ALL',NULL,NULL,NULL,NULL,'10100','Using where; Distinct; Using join buffer'
 '1','SIMPLE','LOAD','ALL',NULL,NULL,NULL,NULL,'13452','Using where; Distinct; Using join buffer'

有没有人对如何优化一个有这么多内部连接的查询有什么想法？

Answer 1

SELECT DISTINCT test.ORDER.PLANT_NAME FROM test.ORDER INNER JOIN test.LOAD ON test.ORDER.ORDER_CODE = test.LOAD.ORDER_CODE AND test.ORDER.ORDER_DATE = test.LOAD.ORDER_DATE AND test.ORDER.PROD_CODE = test.LOAD.PROD_CODE AND test.ORDER.REMOVE_RSN_CODE = test.LOAD.REMOVE_RSN_CODE INNER JOIN test.TRUCK ON test.TRUCK.truck_code = test.LOAD.TRUCK_CODE WHERE test.TRUCK.hler_code = "4000584" ORDER BY PLANT_NAME ASC;

这应该对您帮助不大，但请尝试对列进行索引。