Take the 2-minute tour ×
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. It's 100% free, no registration required.

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:

  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).
share|improve this question
    
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? –  FreshPhilOfSO May 13 '12 at 17:25
    
@Phil yes, I added description to clarify the issue. –  All May 13 '12 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 '12 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 '12 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 ;) –  All May 14 '12 at 11:41
show 1 more comment

1 Answer

up vote 1 down vote accepted

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:

  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.

share|improve this answer
    
Thanks for your consideration, but it will not be practical as sub-SELECT is slow. –  All May 17 '12 at 17:34
    
@Ali just a guess - perhaps it could be faster with an index on tag_map.post_id –  w0lf May 17 '12 at 18:34
1  
how it can be beneficial? indexes are always good for speed. –  All May 17 '12 at 18:55
1  
"sub-SELECT is slow" + "indexes are always good for speed" = probably a faster sub-select –  w0lf May 17 '12 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 –  w0lf May 17 '12 at 19:11
add comment

Your Answer

 
discard

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

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