Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.