1

I'm trying to determine the best way to find all posts that have an exact match with a list of user tags. For example, user has tag IDs: 1,5,9, and 11, while post has tag IDs 1, 5 and 9, thus this is selected as a valid post to display to the user.

The only way I could think of to do this is to count the number of unique tags for each post then count the number of tags a user has in common with the post, compare two values, if equal then it is a match (see query below). Is this truly the best way?

Schema:

Tags
Posts
Posts_tags (tag_id, post_id) unique enforced
Users
Users_tags (tag_id, user_id) unique enforced

Current query (broken down so more legible): Sub-Query #1:

SELECT     COUNT(*) as numrows, pt.post_id AS id
FROM       users_tags AS ut
INNER JOIN posts_tags AS pt
ON         ut.tag_id = pt.tag_id
WHERE      ut.user_id = 1 
GROUP BY   pt.post_id

Sub-Query #2:

SELECT   COUNT(*) AS numrows, post_id AS id
FROM     posts_tags 
GROUP BY post_id

Master Query: 

SELECT     t1.id, t2.id 
FROM       (Sub-Query #1) t1 
INNER JOIN (Sub-Query #2) t2 
ON         (t1.numrows = t2.numrows)
AND        (t1.id = t2.id)

Also, would a graph database like neo4j be better at this type of query?

Improve this question

1 Answer 1

Reset to default
0

If you are using MySQL 5.6 or later, then the query will be reasonably efficient. If using an older version, it is "order N*N", which is very slow for large subqueries.

Improve this answer
3
  • Ah, okay. I used MySQL 5.7. Took ~1.6 seconds to go through 665K post_tag rows and compare them to 1.9K tag_user rows. I made this data set 10X worse than what actual conditions would be. Next, to compare against neo4j with similar data set. The 1.6s works perfectly for my situation since my application doesn't require real-time. On a side-note, it appears that PostgreSQL performs counts more slowly than Mysql 5.7
    – medntech
    Commented Mar 21, 2017 at 4:04
  • EXPLAIN SELECT ... will probably say "auto-key", meaning that MySQL's Optimizer dynamically created an index for a subquery to make the JOIN faster.
    – Rick James
    Commented Mar 21, 2017 at 5:19
  • Yes, it does. I guess for large data sets I have to accept larger return time if I keep with MySQL. No biggie since I can narrow down the range of posts as the user views them! Thanks!
    – medntech
    Commented Mar 21, 2017 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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