I have similar to StackExchange network that utilises tagged questions.

database structure

In my web application Users also can find questions by tags. The query below returns a set of tagged questions:

SELECT question.*, user_activity.* FROM question
    JOIN user_activity ON ua_id = qs_id
    JOIN question_tag ON qt_question_id = qs_id
    JOIN tag ON qt_tag_id = tag_id
WHERE tag_name IN (:tags)
GROUP BY qs_id
HAVING count(tag_id) >= :tagCount

Where :tags is array of tag names and :tagCount == count for this array. In my opinion this query has bad practice even though it does things right.

Are there any ways to optimise this query?

  • Looks fine, however I would use an array of tag_id. That way, I believe, the query would work faster – cha Mar 20 '14 at 22:27
  • @cha tag_name unique column. – Anton Dozortsev Mar 20 '14 at 23:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.