Stack Stack - 6 months ago 10
SQL Question

How to replace OR operator with UNION operator?

Here is my query:

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;


Please focus on this line of query above:

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


As you see, there is
OR
logical operator between those two conditions. As you know,
OR
usually prevents effective use of indexes. So when there is huge data, the performance of my query is really weak.

How can I improve it? Actually I'm trying to replace
OR
with
UNION
, but as you see my query is too long .. So is there any idea?




EDIT: Here is the result of
EXPLAIN
: (for a really short dataset -- overall 20 rows in)

enter image description here

Answer

The first thing I would try is a subquery:

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

Note: This really wants indexes on quanda(id) and quanda(related) for performance.

If few rows are selected, then this might be much faster.