jcink Posted June 10, 2007 Share Posted June 10, 2007 Hi, SQL version: 4.1.21 This query: SELECT t.tid, t.title, t.starter_name, t.starter_id, p.post_date, p.post, p.pid FROM freeto_topics t LEFT JOIN freeto_posts p ON (p.new_topic = 1 AND p.topic_id = t.tid) WHERE t.forum_id IN (7,3,26) AND t.approved=1 ORDER BY t.tid DESC LIMIT 0, 15 is used on a forum. it seems to be doing a filesort. here is the EXPLAIN for this query: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t range forum_id forum_id 4 NULL 23 Using where; Using filesort 1 SIMPLE p ref topic_id topic_id 4 freeto.t.tid 49 How can I get rid of this...? forum_id has an index on tid so I dont understand why it's doing a file sort. More info can be posted if needed, I'm confused with this index stuff and I've read the manuals, but I need some help. Thanks in advance. Quote Link to comment Share on other sites More sharing options...
fenway Posted June 11, 2007 Share Posted June 11, 2007 The index is ascending. Quote Link to comment Share on other sites More sharing options...
jcink Posted June 12, 2007 Author Share Posted June 12, 2007 Hi, What do you mean by ascending? Are you saying it's indexed for an ASC query, but not a DESC query? Quote Link to comment Share on other sites More sharing options...
jcink Posted June 13, 2007 Author Share Posted June 13, 2007 Sorry to be annoying, I HATE bumping support threads, but I tried googling around for help on what you mean but nothing... Quote Link to comment Share on other sites More sharing options...
fenway Posted June 17, 2007 Share Posted June 17, 2007 Hi, What do you mean by ascending? Are you saying it's indexed for an ASC query, but not a DESC query? Yup... try it the other way, and check the EXPLAIN output. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.