Steven, In your case, you query doesn't even use an index. And you are using an order by DESC. Now what I would recommend is something like this, change your query just to test this out.
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; Select * from p_cat inner join p_ad on p_cat.id = p_ad.cat and p_cat.lft = 4 Order by p_ad.date ASC limit 0,30. Also add an index on id + lft on the p_cat table. And you also don't have an index on p_ad.date which is what you are trying to order by. Sorry, I had to rewrite the query because aliases drive me insane. Now in this case, you will see that with lft I have it set to do an exact match, not a range which is what between will give you. If you only were doing between two numbers like 4,5 or 100,101, I would personally recommend using IN. But that's my preference. Now with your order by, if you do the order by ASC, you won't have many if any problems. But you as a lot of people need to order by DESC, which mysql doesn't support very well, at least if you use explain. But there are work arounds to solve the problem, if you are like me and want to have 0 slow queries. Donny > -----Original Message----- > From: Steven Ducat [mailto:[EMAIL PROTECTED] > Sent: Sunday, April 11, 2004 5:32 PM > To: [EMAIL PROTECTED] > Subject: How can I avoid filesort with BETWEEN and ORDER BY > > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
