Workaround for ORDER BY DESC

[Steven Ducat]

I have 2 tables and 1 query. The problem is when I implement ORDER BY
p.date DESC it hits the wall.
I understand that MySQL is not the best at ORDER BY DESC so I am after
some tips on possible workarounds to avoid using ORDER BY DESC.
The site will list classifieds ads so I need to display them from newest
to oldest using a timestamp.
INDEXES
Also as I have been playing with indexes for so long now still trying to
understand them. I still have mixed signals to the way they work. When
MySQL performs a query can it only use 1 index at a time or can it use
several individual indexes. Advice I have been given is to place a
seperate index on each column.
If I was to perform the following query:
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;
then I would expect it to use the cat_date index on table p_ad as it can
only use 1 index but if I am to believe others I should place a seperate
index each on p_ad.cat and p_ad.date and it could use both in the same
query. I look forward to some facts on this issue as I cant seem to
catch on.
Sorry to go on a bit but this is doing my head in.

I look forward to your help..

Cheers

Steven.....





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                           |
+-------+-------+----------------------------+------+---------+------+-------+---------------------------------+ 



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