mysql - 向查询添加 OR 条件会减慢速度

Question

我向查询添加了 OR 条件，查询执行时间从不到 0.5 秒增加到 15 秒以上。 where 子句中使用的所有列都被索引。想知道如何优化此查询。

SELECT SQL_NO_CACHE COUNT(DISTINCT wo.id) AS totrows
FROM wo

LEFT JOIN wu
ON wu.id = wo.id

LEFT JOIN bu
ON bu.id = wu.id

LEFT JOIN ub
ON ub.uid = bu.id

JOIN u
ON u.id = wo.created_by

LEFT JOIN ws
ON ws.wo_id = wo.id, b

WHERE (wo.bid = '13'
AND (wo.created_by IN('2506') OR wo.uid IN('2506') )
AND wo.status != 5
AND ( wo.uid = '' || wu.uid in ( '406', 0) || wo.uid IS NULL )
AND wo.ut = 0
AND ( (wo.wt = 'unit' AND (wo.archive != 1 OR wo.archive IS NULL) ) OR wo.wt = 'common' )) OR (ws.uid = 2506 )

删除 OR (ws.uid = 2506 ) 使查询变得非常快。

Answer 1

我最终像这样使用 SELECT COUNT(*) FROM (Query1 union Query2)。到目前为止看起来不错。

SELECT COUNT(*) AS totrows FROM (
    SELECT SQL_NO_CACHE COUNT(DISTINCT wo.id) AS totrows
    FROM wo

    LEFT JOIN wu
    ON wu.id = wo.id

    LEFT JOIN bu
    ON bu.id = wu.id

    LEFT JOIN ub
    ON ub.uid = bu.id

    JOIN u
    ON u.id = wo.created_by

    LEFT JOIN ws
    ON ws.wo_id = wo.id

    WHERE (wo.bid = '13'
    AND (wo.created_by IN('2506') OR wo.uid IN('2506') )
    AND wo.status != 5
    AND ( wo.uid = '' || wu.uid in ( '406', 0) || wo.uid IS NULL )
    AND wo.ut = 0
    AND ( (wo.wt = 'unit' AND (wo.archive != 1 OR wo.archive IS NULL) ) OR wo.wt = 'common' )) OR (ws.uid = 2506 )

    UNION

    select DISTINCT wo.id from wo LEFT JOIN ws ON ws.wo_id = wo.id WHERE ws.uid = 2506

) AS T