mysql - 如何用 UNION 运算符替换 OR 运算符？

Question

这是我的查询:

SELECT 
    h.id,
    h.subject,
    h.body matnF,
    h.amount,
    h.keywords tags,
    h.closed,
    h.author_id author,
    h.AcceptedAnswer,
    h.type,
    h.visibility,
    h.date_time,
    v.value AS vote_value,
    u.reputation,
    u.user_fname,
    u.user_lname,
    u.avatar,
    (h.author_id = user_id1) as hasUserId,
    (select COALESCE(sum(vv.value), 0)
     from votes vv
     where h.id = vv.post_id and vv.table_code = '$this->table_code'
    ) as total_votes,
    (select count(1)
     from favorites ff
     where h.type = 0 and h.id = ff.post_id and ff.table_code = '$this- >table_code'
    ) as total_favorites,
    CASE WHEN h.type = 1 THEN '0'
         WHEN h.amount IS NULL OR h.author_id = :user_id2 THEN '1'
    ELSE EXISTS( select 1
            from money m
            where m.user_id = :user_id3 and m.post_id = h.id
           )END paid,
    CASE WHEN h.type = 0 AND f.id IS NOT NULL THEN '2'
    ELSE '3'
    END AS favorite
FROM qanda h
LEFT JOIN votes v ON h.id = v.post_id AND v.user_id = :user_id4 AND   v.table_code = '$this->table_code'
LEFT JOIN favorites f ON h.type = 0 AND h.id = f.post_id AND f.user_id = :user_id5 AND f.table_code = '$this->table_code'
LEFT JOIN users u ON h.author_id = u.id and h.visibility = 1
WHERE h.id = :id1 OR h.related = :id2
ORDER BY h.type , h.AcceptedAnswer DESC , h.date_time
LIMIT 20;

请关注上面的这一行查询:

WHERE h.id = :id1 OR h.related = :id2

如您所见，这两个条件之间存在 OR 逻辑运算符。如您所知，OR 通常会阻止索引的有效使用。所以当有大量数据时，我的查询性能真的很弱。

我该如何改进它？实际上，我正在尝试将 OR 替换为 UNION，但是如您所见，我的查询太长了..那么有什么想法吗？

---

编辑:这是 EXPLAIN 的结果:(对于一个非常短的数据集——总共 20 行)

Answer 1

我首先要尝试的是子查询:

from ((select q.* from quanda q where q.id = :id1) union
      (select q.* from quanda q where q.related = :id2)
     ) left join
     . . .

注意:这确实需要 quanda(id) 和 quanda(related) 上的索引以提高性能。

如果选择的行很少，那么这可能会快得多。