Cant get rid of filesort in simple query I have two tables. One for texts and one to assign those texts to categories. I want to select texts of one specific category and order them by last change date. So, I am using this query:

SELECT 
     t2.text_id
FROM
    texts_to_categories AS t1,
    texts AS t2
WHERE
    t1.category_id = 123 
    AND t2.text_id = t1.text_id
ORDER BY
    t2.time_changed DESC
LIMIT 10


I tried a lot of index combinations but I keep getting a temporary and filesort:

id  select_type table   type    possible_keys                   key         key_len ref         rows    extra
1   SIMPLE      t1      ref     text_id,text_id_2,category_id   category_id 5       const       203     Using where; Using temporary; Using filesort
1   SIMPLE      t2      eq_ref  PRIMARY,text_id                 PRIMARY     4       text_id     1       Using where


Edit: Here is a CREATE TABLE for the two tables:

CREATE TABLE `texts` (
`text_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`time_changed` datetime DEFAULT NULL,
PRIMARY KEY (`text_id`),
KEY `time_changed` (`time_changed`)
) ENGINE=InnoDB AUTO_INCREMENT=132207 DEFAULT CHARSET=utf8

CREATE TABLE `texts_to_categories` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`text_id` int(11) DEFAULT NULL,
`category_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `text_id` (`text_id`,`category_id`),
KEY `text_id_2` (`text_id`),
KEY `category_id` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=740789 DEFAULT CHARSET=utf8
You can try to add an index on time_changed:

alter table texts add index (time_changed);


this could get rid of Using temporary and filesort. But you need to define indexes for your query because the query plan show Using where instead of more healthy Using index.



Updated

Maybe the main culprit for the temporary/filesort is the order of the join. It is not good practice to force the optimizer without a good reason, but you could try to force the join order to test if the temporary/filesort goes away. I have swapped the tables and added a straight_join clause:

SELECT straight_join
     t2.text_id
FROM
    texts AS t2,
    texts_to_categories AS t1
WHERE
    t1.category_id = 123 
    AND t2.text_id = t1.text_id
ORDER BY
    t2.time_changed DESC
LIMIT 10;


Let me know what is the query plan of the previous query.

I don't think that temporary/filesort is a big deal for few hundred rows. I would let the optimizer do its job. I would be more worried to get a better tables schema. If it is feasible for you, I suggest the following changes to your schema.

For texts_categories your table permits NULL value for text_id and for category_id. So I would delete NULL values:

delete from texts_to_categories  where category_id is NULL or text_id is NULL;

alter table texts_to_categories
change category_id category_id int unsigned not null,
change text_id text_id int unsigned not null;


Next if you don't need texts_categories.id column, I would get rid of it, and I would reorganize indexes:

alter table texts_to_categories
drop column id,
add primary key category_text (category_id, text_id),
drop index text_id,
drop index category_id,
add index text_id;


For table texts I will try time_changed column to do not permit null values:

update texts_bck 
set time_changed = '2010-01-01' -- some default
where time_changed is NULL;


alter table texts
change column time_changed time_changed datetime not null 
default current_timestamp;


I hope this is of help.