dadonk dadonk - 6 months ago 6
SQL Question

Is there a way to make this MySQL query more efficient?

select * from q_and_a qa
where keyword = :keyword_top
or id in (
select qk.question_id
from question_keywords qk
inner join q_and_a qa on qa.id = qk.question_id
inner join keywords k on k.id = qk.keyword_id
where k.keyword = :keyword_bottom
)

Answer

If I'm understanding your query correctly, you don't need the subquery, but rather outer joins:

select q.*
from q_and_a q
    left join question_keywords qk on q.id = kq.question_id 
    left join keywords k on k.id = gk.keyword_id
where q.keyword = :keyword_top or 
    k.keyword = :keyword_bottom

You may need to use distinct with this, depends on the sample data and desired results.


Actually since this is really a question about performance, this may or may not make it faster. As usual, it just depends.

A couple other variations to your existing query would include using exists or using union all instead of or. You have to run the comparisons to see what works best in your situation. Also your indices and keys need to be defined appropriately.

Comments