я есть таблица, которая выглядит так:

категория

category_idимяcategory_seo_friendly_urlleft_idright_id

Когда я запускаю такой запрос,это займет почти 1 секунду:

SELECT node.category_id                                       AS node_category_id,
       node.category_seo_friendly_url,
       node.name,
       ( COUNT(parent.category_id) - ( sub_tree.depth + 1 ) ) AS depth
FROM   category AS node,
       category AS parent,
       category AS sub_parent,
       (SELECT node.category_id,
               ( COUNT(parent.category_id) - 1 ) AS depth
        FROM   category AS node,
               category AS parent
        WHERE  node.left_id BETWEEN parent.left_id AND parent.right_id
               AND node.category_id = 2
        GROUP  BY node.category_id
        ORDER  BY node.left_id)AS sub_tree
WHERE  node.left_id BETWEEN parent.left_id AND parent.right_id
       AND node.left_id BETWEEN sub_parent.left_id AND sub_parent.right_id
       AND sub_parent.category_id = sub_tree.category_id
GROUP  BY node.category_id
HAVING depth > 0
       AND depth <= 1
ORDER  BY node.name ASC

Когда я делаюEXPLAINЯ получаю следующее:

id    select_type    table       type    possible_keys                         key       key_len    ref     rows    Extra
1     PRIMARY        <derived2>  system  NULL                                  NULL      NULL       NULL    1       Using temporary; Using filesort
1     PRIMARY        sub_parent  const   PRIMARY,category_id,left_id,right_id  PRIMARY   4          const   1     
1     PRIMARY        node        ALL     left_id                               NULL      NULL       NULL    748     Using where
1     PRIMARY        parent      ALL     left_id,right_id                      NULL      NULL       NULL    748     Range checked for each record (index map: 0x30)
2     DERIVED        node        const   PRIMARY,category_id,left_id           PRIMARY   4                  1     
2     DERIVED        parent      range   left_id,right_id                      left_id   5          NULL    17      Using where

Есть идеи, что происходит?Я не могу позволить себе это около 1 секунды времени исполнения.

ОБНОВИТЬ:

-- phpMyAdmin SQL Dump
-- version 3.3.9
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 16, 2011 at 10:58 PM
-- Server version: 5.0.91
-- PHP Version: 5.2.6
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `foobar`
--
-- --------------------------------------------------------
--
-- Table structure for table `category`
--
CREATE TABLE IF NOT EXISTS `category`
  (
     `category_id`               INT(11) NOT NULL AUTO_INCREMENT,
     `name`                      CHAR(255) DEFAULT NULL,
     `category_seo_friendly_url` CHAR(255) DEFAULT NULL,
     `left_id`                   INT(11) DEFAULT '1',
     `right_id`                  INT(11) DEFAULT '2',
     PRIMARY KEY (`category_id`),
     UNIQUE KEY `seo_friendly_url_UNIQUE` (`category_seo_friendly_url`),
     KEY `category_id` (`category_id`),
     KEY `left_id` (`left_id`),
     KEY `right_id` (`right_id`)
  )
ENGINE=MyISAM
DEFAULT CHARSET=latin1
AUTO_INCREMENT=765;