我有一个复杂的查询,该查询总共需要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.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;
如果没有OR
其中一个联接,如何实现相同的效果?
作为a进行重写UNION
很简单,复制源并删除每个中的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
吗?
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句