联合还是有条件的加入？

Question

我有两个疑问，我要加入使用联盟。请参见下面的查询。

SELECT * FROM 
(
    SELECT so.`so-no`, so.`so-date`, po.`po-no`, po.`po-date`, si.`si-no`, si.`si-date`, pay.`or-no`, pay.`or-date`, c.`client-name`, p.product_name, p.product_desc, sod.qty, sod.unitsrp, sod.totalsrp, pod.unitcost, pod.totalcost, sod.ma, so.`withholding-tax`, concat(s.firstname, ' ' ,s.lastname) as 'sales'
    FROM `sales-order` so
    LEFT JOIN so_details sod ON so.`so-number` = sod.so_number
    LEFT JOIN `po_details` pod ON sod.sod_no = pod.sod_no
    LEFT JOIN `purchase-order` po ON pod.po_number = po.`po-number`
    LEFT JOIN si_details sid ON pod.so_number = sid.so_number
    LEFT JOIN `sales-invoice` si ON sid.si_number = si.`si-number`
    LEFT JOIN or_details ord ON sid.si_number = ord.si_number
    LEFT JOIN payment pay ON ord.or_number = pay.`or-number`
    LEFT JOIN clients c ON so.`client-no` = c.`client-no`
    LEFT JOIN products p ON pod.product_id = p.product_id
    LEFT JOIN sales s ON c.`sales-id` = s.`sales-id`
    LEFT JOIN `sales-territory` st ON s.`sales-territory-no` = st.`sales-territory-no`
    LEFT JOIN `product-managers` pm ON p.pm_id = pm.`pm-id`
    WHERE so.`so-date` between '2017-01-01' and '2017-12-31' and p.product_category LIKE '%projector%' and pm.`pm-id` = '31'and so.adv_so = 'no'
    UNION ALL   
    SELECT so.`so-no`, so.`so-date`, po.`po-no`, po.`po-date`, si.`si-no`, si.`si-date`, pay.`or-no`, pay.`or-date`, c.`client-name`, p.product_name, p.product_desc, asod.qty, asod.unitsrp, asod.totalsrp, apod.unitcost, apod.totalcost, asod.ma, so.`withholding-tax`, concat(s.firstname, ' ' ,s.lastname) as 'sales'
    FROM `sales-order` so
    LEFT JOIN `aso_details` asod ON so.`so-number` = asod.so_number
    LEFT JOIN `purchase-order` po ON asod.po_number = po.`po-number`
    LEFT JOIN si_details sid ON asod.so_number = sid.so_number
    LEFT JOIN `sales-invoice` si ON sid.si_number = si.`si-number`
    LEFT JOIN or_details ord ON sid.si_number = ord.si_number
    LEFT JOIN payment pay ON ord.or_number = pay.`or-number`
    LEFT JOIN clients c ON so.`client-no` = c.`client-no`
    LEFT JOIN products p ON asod.product_id = p.product_id
    LEFT JOIN apo_details apod ON asod.pod_no = apod.pod_no
    LEFT JOIN sales s ON c.`sales-id` = s.`sales-id`
    LEFT JOIN `sales-territory` st ON s.`sales-territory-no` = st.`sales-territory-no`
    LEFT JOIN `product-managers` pm ON p.pm_id = pm.`pm-id`
    WHERE so.`so-date` between '2017-01-01' and '2017-12-31' and p.product_category LIKE '%projector%' and pm.`pm-id` = '31'and so.adv_so = 'yes'
) a
ORDER BY a.`so-no` ASC

我想知道在性能上使用MySQL条件联接是否会更快。如果某列具有此值(是/否)，则类似于从特定表中选择。如果它更快，我如何开始查询？

Answer 1

使用JOIN而不是LEFT JOIN，除非您期望“右”行会丢失。它混淆了读者和优化器。

看看这样做是否更快：

SELECT *
    FROM (
        SELECT with some of the first JOINs ((so, po, ...))
        UNION ALL
        SELECT with different first JOINs  ((aso, apo, ...
         )
    JOIN the last few joins ((clients, products, sales, territories, managers. ...))
    ORDER BY `so-no`