Nested set and query optimization

[bmga]

Hi,

I´m working on a web application using MySQL as database and I think I could use some advice on how to optimize a query in one part.

Basicly there is three tables that is relevant to this thread: images, images_keywords and keywords.

A simplified version of the tables --

CREATE TABLE `images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `caption` text NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
CREATE TABLE `images_keywords` (
  `image_id` int(11) NOT NULL,
  `keyword_id` int(11) NOT NULL,
  UNIQUE KEY `image_id` (`image_id`,`keyword_id`),
  KEY `image_id_2` (`image_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
CREATE TABLE `keywords` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `lft` int(11) DEFAULT NULL,
  `rght` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `lft` (`lft`,`rght`),
  KEY `rght` (`rght`,`lft`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

What we would like to do is to find and count (for pagination) unique images that is related to a keyword or any of it´s children. At the moment we have a working, but slow query

SELECT COUNT(DISTINCT(images.id)) as count
  FROM images_keywords
LEFT
  JOIN keywords
    ON images_keywords.keyword_id = keywords.id
LEFT
  JOIN images
    ON images_keywords.image_id = images.id
 WHERE images.status = 1
   AND keywords.lft BETWEEN 150 AND 553;
 
1 row in set (2.28 sec)

+----+-------------+-----------------+--------+---------------------+----------+---------+---------------------------------+--------+-------------+
| id | select_type | table           | type   | possible_keys       | key      | key_len | ref                             | rows   | Extra       |
+----+-------------+-----------------+--------+---------------------+----------+---------+---------------------------------+--------+-------------+
|  1 | SIMPLE      | images_keywords | index  | image_id,image_id_2 | image_id | 8       | NULL                            | 300110 | Using index | 
|  1 | SIMPLE      | images          | eq_ref | PRIMARY,status      | PRIMARY  | 4       | demo.images_keywords.image_id   |      1 | Using where | 
|  1 | SIMPLE      | keywords        | eq_ref | PRIMARY,lft         | PRIMARY  | 4       | demo.images_keywords.keyword_id |      1 | Using where | 
+----+-------------+-----------------+--------+---------------------+----------+---------+---------------------------------+--------+-------------+

SELECT DISTINCT(images.id), images.*
  FROM images_keywords
LEFT
  JOIN keywords
    ON images_keywords.keyword_id = keywords.id
LEFT
  JOIN images
    ON images_keywords.image_id = images.id
 WHERE images.status = 1
   AND keywords.lft BETWEEN 150 AND 553
ORDER
   BY images.created DESC
LIMIT 40;
 
40 rows in set (4.71 sec)

+----+-------------+-----------------+--------+---------------------+----------+---------+---------------------------------+--------+----------------------------------------------+
| id | select_type | table           | type   | possible_keys       | key      | key_len | ref                             | rows   | Extra                                        |
+----+-------------+-----------------+--------+---------------------+----------+---------+---------------------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | images_keywords | index  | image_id,image_id_2 | image_id | 8       | NULL                            | 300110 | Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | images          | eq_ref | PRIMARY,status      | PRIMARY  | 4       | demo.images_keywords.image_id   |      1 | Using where                                  | 
|  1 | SIMPLE      | keywords        | eq_ref | PRIMARY,lft         | PRIMARY  | 4       | demo.images_keywords.keyword_id |      1 | Using where; Distinct                        | 
+----+-------------+-----------------+--------+---------------------+----------+---------+---------------------------------+--------+----------------------------------------------+

Any ideas on how I can speed up theese querys ?
Thanks in advance!