How to order query results by tag weight in IN('tag1', 'tag2', 'tag3')

Consider a simple query as

SELECT * FROM posts
JOIN tags USING(post_id)
JOIN tag_map USING(tag_id)
WHERE tags.tag IN('tag1', 'tag2', 'tag3')

tag_map is a many-to-many relationship between tables posts and tags.

In this query, mysql will find all posts tagged by any of these words, and order them by PRIMARY KEY.

How can we order the results by tag weight:

• posts witch tagged with more words in the list (first posts with three tags from the list, then with two words, ...)

• posts witch tagged with first words (first posts tagged with tag1 then posts tagged with tag2).

I did

select * 
from posts p
join tag_map tm on tm.post_id = p.post_id
join tags t on tm.tag_id = t.tag_id
join 
  (select post_id, count(tag_id) as tag_count
   from tag_map
   group by post_id) counts
  on counts.post_id = p.post_id
where t.tag in('tag1', 'tag2', 'tag3')
order by tag_count desc, tag asc

and it seems to work OK according to your specs:

• Order by tag count - I joined with a sub-select that retrieves the tag count per post_id and ordered primarily by that count (descending)

• Order by tag name - after ordering by tagcount/post, the query orders by tag name

Here's the SQLFiddle if you want to experiment more: http://sqlfiddle.com/#!2/8fcd7/9

Update: version of the SQLFiddle with index on (tag_map.post_id): http://sqlfiddle.com/#!2/02b5e/1

PS: this is my first MySQL query, so if there are any criticisms regarding performance/style/anything else, I'd be glad to hear them and learn from them.

StackExchange Database Administrators Q#17800, answer score: 2