Ygthkb

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.