Consider a simple query as

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:

  1. posts witch tagged with more words in the list (first posts with three tags from the list, then with two words, ...)
  2. posts witch tagged with first words (first posts tagged with tag1 then posts tagged with tag2).
  • No context or other column names in the question except PKs. I'm assuming tag_map is a many<>many relationship between forum/blog posts and tags that have been associated with each post?
    – Philᵀᴹ
    May 13, 2012 at 17:25
  • @Phil yes, I added description to clarify the issue.
    – Googlebot
    May 13, 2012 at 17:52
  • 1
    "and order them by PRIMARY KEY" No it won't! There's no ORDER BY so the results won't be ordered.
    – Jannes
    May 14, 2012 at 8:55
  • Also, with your query a post with 3 tags will be in the resultset 3 times! So you probably want to add a GROUP BY post_id anyway. Then just a COUNT(*) AS cnt in the select and add ORDER BY cnt DESC.
    – Jannes
    May 14, 2012 at 9:03
  • @Jannes it will not ORDER by PRIMARY KEY, but the results will be retrieved in this order (i.e. order of INSERT). At least in my experience (maybe by chance ;)
    – Googlebot
    May 14, 2012 at 11:41

1 Answer 1


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
  (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:

  1. 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)
  2. 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.

  • Thanks for your consideration, but it will not be practical as sub-SELECT is slow.
    – Googlebot
    May 17, 2012 at 17:34
  • @Ali just a guess - perhaps it could be faster with an index on tag_map.post_id May 17, 2012 at 18:34
  • 1
    how it can be beneficial? indexes are always good for speed.
    – Googlebot
    May 17, 2012 at 18:55
  • 1
    "sub-SELECT is slow" + "indexes are always good for speed" = probably a faster sub-select May 17, 2012 at 19:04
  • 1
    I've made a test with the index. At this data quantity there's obviously no improvement in execution time, but I think the Execution Plan shows an improvement (on the last line). I've added a link to the new SQLFiddle in my post May 17, 2012 at 19:11

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.