How can I avoid filesort with BETWEEN and ORDER BY

[Steven Ducat]

I am trying to optimize a query using both BETWEEN and ORDER BY but 
after months of reading and research I still can not get the hang of 
this. Details are as follows:

2 Tables

CREATE TABLE `p_ad` (
 `id` int(11) NOT NULL auto_increment,
 `cat` mediumint(9) NOT NULL default '0',
 `title` varchar(50) default NULL,
 `description` text,
 `location` varchar(50) default NULL,
 `pcode` varchar(8) default NULL,
 `pcode_id` smallint(4) default NULL,
 `ph` varchar(50) default NULL,
 `email` varchar(50) default NULL,
 `user_id` int(11) NOT NULL default '0',
 `date` timestamp(14) NOT NULL,
 `price` decimal(10,2) default NULL,
 `email_priv` tinyint(1) default '0',
 PRIMARY KEY  (`id`),
 KEY `cat_pc_date` (`cat`,`pcode_id`,`date`),
 KEY `c_p_d` (`cat`,`pcode`,`date`),
 KEY `user` (`user_id`),
 KEY `cat_date` (`cat`,`date`)
) TYPE=MyISAM;
CREATE TABLE `p_cat` (
 `id` mediumint(9) NOT NULL auto_increment,
 `name` varchar(50) NOT NULL default '',
 `parent` mediumint(11) default '0',
 `lft` mediumint(11) NOT NULL default '0',
 `rgt` mediumint(11) NOT NULL default '0',
 PRIMARY KEY  (`id`),
 KEY `LFT` (`lft`),
 KEY `PARENT` (`parent`)
) TYPE=MyISAM;
Query as follows:

EXPLAIN SELECT p.* FROM p_cat c, p_ad p WHERE p.cat = c.id AND c.lft 
BETWEEN 4 AND 5 ORDER BY p.date DESC LIMIT 0,30;
+-------+-------+----------------------------+------+---------+------+-------+---------------------------------+
| table | type  | possible_keys              | key  | key_len | ref  | 
rows  | Extra                           |
+-------+-------+----------------------------+------+---------+------+-------+---------------------------------+
| p     | ALL   | cat_pc_date,c_p_d,cat_date | NULL |    NULL | NULL | 
60002 | Using temporary; Using filesort |
| c     | range   | PRIMARY,LFT                   | LFT    |            
 3 | NULL |         1 | Using where                           |
+-------+-------+----------------------------+------+---------+------+-------+---------------------------------+

Is there any way I can get a query like this to avoid using a temporary 
table and filesort.

??





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]