GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Order by "during" join

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Order by "during" join

    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!

  • #2
    Can you please do 'show create table' for tables used in query?
    Also please run the same query with 'EXPLAIN' before it, and copy-paste result here.

    Comment

    Working...
    X