mysql - 如何优化这个 mysql select + 多连接查询？

Question

我正在运行此查询，但速度很慢。最后一个左连接上的 OR 子句使它陷入困境，但我想不出另一种方法来构造查询以获得我正在寻找的结果:

SELECT *,
       DATE_FORMAT(tbl1.pub, '%m/%d/%Y') AS pub_a,
       CONCAT(tbl1.code1, tbl1.code2) AS code_a
FROM (tbl1
      INNER JOIN tbl2 ON tbl1.pkid=tbl2.fkid
      LEFT JOIN tbl3 ON tbl1.pkid=tbl3.fkid)
LEFT JOIN tbl4 ON tbl1.person=tbl4.code OR tbl3.person=tbl4.code
WHERE tbl1.subcat != ''
  AND tbl1.pub < '2014-01-15 13:20:23'
  AND tbl1.exp > '2014-01-15 13:20:23'
ORDER BY tbl1.pub DESC 
LIMIT 0, 50

Answer 1

不幸的是，优化 on 子句中的 or 并不容易。您可以将其拆分为两个查询，并结合 union:

select *
from ((SELECT *,
              DATE_FORMAT(tbl1.pub, '%m/%d/%Y') AS pub_a,
              CONCAT(tbl1.code1, tbl1.code2) AS code_a
        FROM (tbl1
              INNER JOIN tbl2 ON tbl1.pkid=tbl2.fkid
              LEFT JOIN tbl3 ON tbl1.pkid=tbl3.fkid
              )
              LEFT JOIN tbl4 ON tbl3.person=tbl4.code
        WHERE tbl1.subcat != ''
          AND tbl1.pub < '2014-01-15 13:20:23'
          AND tbl1.exp > '2014-01-15 13:20:23'
        ORDER BY tbl1.pub DESC 
        LIMIT 0, 50
       ) union
       (SELECT *,
              DATE_FORMAT(tbl1.pub, '%m/%d/%Y') AS pub_a,
              CONCAT(tbl1.code1, tbl1.code2) AS code_a
        FROM (tbl1
              INNER JOIN tbl2 ON tbl1.pkid=tbl2.fkid
              LEFT JOIN tbl3 ON tbl1.pkid=tbl3.fkid
              )
              LEFT JOIN tbl4 ON tbl1.person=tbl4.code
        WHERE tbl1.subcat != ''
          AND tbl1.pub < '2014-01-15 13:20:23'
          AND tbl1.exp > '2014-01-15 13:20:23'
        ORDER BY tbl1.pub DESC 
        LIMIT 0, 50
       )
      ) t
order by pub desc
limit 0, 50