Needing help with filesort

[jcink]

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.

[fenway]

The index is ascending.

[jcink]

Hi,

 

What do you mean by ascending? Are you saying it's indexed for an ASC query, but not a DESC query?

[jcink]

Sorry to be annoying, I HATE bumping support threads, but I tried googling around for help on what you mean but nothing...

[fenway]

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.