Slow query from getting the intersection of news_tags and users_tags

Question

I have two tables: "news_tags" and "users_tags". Now I want to get the appropriate users for a particular news. I get the right results with my query, but it's so slow.

Here is my query so far:

SELECT `users_id`
FROM `users_tags`
WHERE `tags_id` IN (SELECT `tags_id` FROM `news_tags`
                    WHERE `news_id` = '$news_id')
GROUP BY `users_id`
ORDER BY `users_id`

Is there a way to do it much faster?

Many thanks an greets

Answer 1

Converting to an INNER JOIN will give you a boost.

SELECT `users_id` FROM `users_tags` u INNER JOIN news_tags t 
ON u.tags_id = t.tags_id 
WHERE `news_id` = '$news_id' GROUP BY `users_id` ORDER BY `users_id`

Then make sure that you have an index on the news_id column of news_tags and also an index on tags_id of users_tags

In fact a composite index on users_tags(tags_id, users_id) might be even better. All these will need to be decided after looking at your EXPLAIN output.