MySQL Forums :: Performance :: Slow many-to-many query with distinct and order by

Hi,

I´m having performance trouble with a query.

SELECT DISTINCT(`Image`.`id`), `Image`.`title`, `Image`.`filename` 
  FROM `images_keywords` AS `ImagesKeyword` 
LEFT 
  JOIN `images` AS `Image` ON (`ImagesKeyword`.`image_id` = `Image`.`id`) 
 WHERE `ImagesKeyword`.`keyword_id` IN (110000010, 110000109, 110000103, 110000111, 110000110, 110000105, 110000108, 110000107, 110000102, 110000106, 110000104) 
   AND `Image`.`published` = 1 
 ORDER 
    BY `Image`.`created` DESC 
 LIMIT 40;

40 rows in set (6.09 sec)

What I want to do is to find published images that is related to a keyword or any of it´s children and then order by creation date descendingly. For keyword hierarchies I´m using the Nested Set Model, described in http://dev.mysql.com/tech-resources/articles/hierarchical-data.html.
Images and keywords has a many-to-many relation and is being linked together in the images_keywords table.

The IN clause contains a list of id´s for a keyword + all of it´s children.

What can I do to speed up this query ? Or maybe there is a more effecient way to achieve this ?

I´ve played around alot with different indexes without much success.
The ”bad” parts seems to be distinct and/or combined with the order by clause, the query seems to run OK without theese.


Running an explain gives:

+----+-------------+---------------+--------+---------------------+------------+---------+-------------------------------------+-------+-----------------------------------------------------------+
| id | select_type | table         | type   | possible_keys       | key        | key_len | ref                                 | rows  | Extra                                                     |
+----+-------------+---------------+--------+---------------------+------------+---------+-------------------------------------+-------+-----------------------------------------------------------+
|  1 | SIMPLE      | ImagesKeyword | range  | keyword_id,image_id | keyword_id | 4       | NULL                                | 34081 | Using where; Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | Image         | eq_ref | PRIMARY,published   | PRIMARY    | 4       | prototype.ImagesKeyword.image_id    |     1 | Using where                                               | 
+----+-------------+---------------+--------+---------------------+------------+---------+-------------------------------------+-------+-----------------------------------------------------------+

Thanks.