How to replace the OR operator with the UNION operator?

Question

Here is my request:

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 query line above:

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

As you can see, there is a logical OR operator between these two conditions. As you know, OR usually hinders the efficient use of indexes. Therefore, when there is huge data, the performance of my request is very weak.

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

---

EDIT: Here is the result of EXPLAIN : (for a very short data set - only 20 rows)

Answer 1

The first thing I'll try is the subquery:

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

Note. It really requires quanda(id) and quanda(related) indexes for performance.

If multiple rows are selected, this can be much faster.

Answer 2

You can try:

 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 ORDER BY h.type, /*(tans.id IS NOT NULL) DESC,*/ h.AcceptedAnswer DESC, h.date_time LIMIT 20 UNION SELECT * FROM (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 = :id2 ORDER BY h.type, /*(tans.id IS NOT NULL) DESC,*/ h.AcceptedAnswer DESC, h.date_time LIMIT 20) t; 

Note. Replace UNION with UNION ALL if you want to allow duplicates in the final result set.

Answer 3

If you get any advantage of using UNION instead of OR, make this part of the query as small as it can be, in particular so that the number of columns is minimal. Make an alliance before any other alliance. I suggest the following:

 SELECT h.id , h.subject , h.body AS matnF , h.amount , h.keywords AS tags , h.closed , h.author_id AS 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 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 = 0 AND f.id IS NOT NULL THEN '2' ELSE '3' END AS favorite , 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 FROM ( SELECT q.id , q.author_id , q.visibility , q.type FROM quanda q WHERE q.id = :id1 UNION SELECT q.id , q.author_id , q.visibility , q.type FROM quanda q WHERE q.related = :id2 ) 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