Thanks for your answer.
I tried your example after adding the necessary index and it works as long as I define a single number not a range (i.e. p_cat.lft = 4).
Will keep working on the range part.
What I am more interested in is the workarounds that can be made to improve the ORDER BY DESC problems
as I can do all my queries perfectly withour this order by but as soon as it is added my site gives up. As the site is
displaying classified ads they need to be displayed from the newest to the oldest.
Any more help will be greatlty appreciated.
Cheers
Steve..
Donny Simonton wrote:
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]
