如何处理多个连接 [英] How to handle multiple joins

问题描述

我有一个复杂的查询，需要来自总共4个表的字段。内部联接导致查询花费的时间比它应该多得多。我运行了一个EXPLAIN语句，其视觉效果如下：

这是我的查询：

  SELECT 
 pending_corrections.corrected_plate，pending_corrections.seenDate 
 FROM 
（pending_corrections 
 INNER JOIN cameras ON pending_corrections.camerauid = cameras.camera_id）
 INNER JOIN 
 vehicle_vrn ON（pending_corrections.corrected_plate = vehicle_vrn.vrn500 
 OR pending_corrections。 
 INNER JOIN 
 vehicle_ownership ON vehicle_vrn.fk_sysno = vehicle_ownership.fk_sysno 
 WHERE 
 pending_corrections.seenDate> ='2015-01-01 00:00： 00'
 AND pending_corrections.seenDate< ='2015-01-31 23:59:59'
 ORDER BY pending_corrections.corrected_plate，pending_corrections.seenDate ASC; 
  

如何实现相同的效果，但没有 OR >是简单的，复制源并删除每个中的 OR ed条件之一：

  SELECT 
 pending_corrections.corrected_plate，pending_corrections.seenDate 
 FROM 
（pending_corrections 
 INNER JOIN cameras ON pending_corrections.camerauid = cameras.camera_id）
 INNER JOIN 
 vehicle_vrn ON（pending_corrections.corrected_plate = vehicle_vrn.vrn500）
 INNER JOIN 
 vehicle_ownership ON vehicle_vrn.fk_sysno = vehicle_ownership.fk_sysno 
 WHERE 
 pending_corrections.seenDate> =' 2015-01-01 00:00:00'
 AND pending_corrections.seenDate< ='2015-01-31 23:59:59'
 
 union 
 
 SELECT 
 pending_corrections.corrected_plate，pending_corrections.seenDate 
 FROM 
（pending_corrections 
 INNER JOIN cameras ON pending_corrections.camerauid = cameras.camera_id）
 INNER JOIN 
 vehicle_vrn ON pending_corrections.corrected_plate = vehicle_vrn.vrnno）
 INNER JOIN 
 vehicle_ownership ON vehicle_vrn.fk_sysno = vehicle_ownership.fk_sysno 
 WHERE 
 pending_corrections.seenDate> ='2015-01-01 00:00:00'
 AND pending_corrections.seenDate< ='2015-01-31 23:59:59'
 
 ORDER BY 1,2; 
  

pending_corrections.seenDate ？

I have a complex query which requires fields from a total of 4 tables. The inner joins are causing the query to take much longer than it should. I have run an EXPLAIN statement, whose visual result is attached below:

This is my query:

SELECT 
   pending_corrections.corrected_plate , pending_corrections.seenDate
FROM
    (pending_corrections
    INNER JOIN cameras ON pending_corrections.camerauid = cameras.camera_id)
        INNER JOIN
    vehicle_vrn ON (pending_corrections.corrected_plate = vehicle_vrn.vrn500
        OR pending_corrections.corrected_plate = vehicle_vrn.vrnno)
        INNER JOIN
    vehicle_ownership ON vehicle_vrn.fk_sysno = vehicle_ownership.fk_sysno
WHERE
    pending_corrections.seenDate >= '2015-01-01 00:00:00'
        AND pending_corrections.seenDate <= '2015-01-31 23:59:59'
ORDER BY pending_corrections.corrected_plate , pending_corrections.seenDate ASC;

How can I achieve the same effect but without the OR in one of the joins?

解决方案

Rewriting as a UNION is simple, copy the source and remove one of the ORed conditions in each:

SELECT 
   pending_corrections.corrected_plate , pending_corrections.seenDate
FROM
    (pending_corrections
    INNER JOIN cameras ON pending_corrections.camerauid = cameras.camera_id)
        INNER JOIN
    vehicle_vrn ON (pending_corrections.corrected_plate = vehicle_vrn.vrn500)
        INNER JOIN
    vehicle_ownership ON vehicle_vrn.fk_sysno = vehicle_ownership.fk_sysno
WHERE
    pending_corrections.seenDate >= '2015-01-01 00:00:00'
        AND pending_corrections.seenDate <= '2015-01-31 23:59:59'

union 

SELECT 
   pending_corrections.corrected_plate , pending_corrections.seenDate
FROM
    (pending_corrections
    INNER JOIN cameras ON pending_corrections.camerauid = cameras.camera_id)
        INNER JOIN
    vehicle_vrn ON pending_corrections.corrected_plate = vehicle_vrn.vrnno)
        INNER JOIN
    vehicle_ownership ON vehicle_vrn.fk_sysno = vehicle_ownership.fk_sysno
WHERE
    pending_corrections.seenDate >= '2015-01-01 00:00:00'
        AND pending_corrections.seenDate <= '2015-01-31 23:59:59'

ORDER BY 1,2;

Is there an index on pending_corrections.seenDate?

这篇关于如何处理多个连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！