Hi guys,
I have a many-to-many relationship:
tags has many topics, topics has many tags
I use a relation table to make this relationship:
tags, tags_links (contains tag_id, topic_id) and topics.
I want to select the 10 last updated topics that has a given tag:
SELECT t.topic_id, t.title
FROM tags_links AS tl
JOIN topics AS t ON t.topic_id = tl.topic_id
WHERE tl.tag_id = ‘2’
ORDER BY t.last_post_id DESC
LIMIT 10
It works as expected. However, it seems mysql makes the entire join BEFORE considering the ORDER BY (with table scan of several thousands of records). I’ve tried indexing the topics in several ways:
last_post_id
last_post_id, topic_id
topic_id, last_post_id
It would be nice if you could make it join ONLY the ten last updated topics using some fancy index. But apparently you can’t. Even if I was using ORDER BY t.last_post_id ASC it joins ALL topics before doing the order by. I presumed that it would be faster with ASC as all mysql indexes are stored in ascending order (AFAIK).
The best alternative solution I see is to create a last_post_id-column in the tags_links and index that. That would create some redundant data and increase number of updates when a topic is updated but improve performance magnificently.
Ideas, solution, and/or other alternatives are very welcome!