Re: INDEX DESC

[Michael Stassen]

Matt W wrote:
Hi Michael,
----- Original Message -----
From: "Michael Stassen"
I expect he's referring to mysql's poor performance when doing "ORDER BY
indexed_column DESC" relative to "ORDER BY indexed_column ASC".
The performance is only poor when using an index for DESC, *if the index is
PACKED*.  (There's different criteria that determines if a certain index is
packed.)  Otherwise, it should be pretty much exactly the same as ASC.
Matt
You, Jeremy, and Paul are in complete agreement on this, so it must be true, 
but I'm a bit surprised.  There have been quite a few threads about the 
slowness of ORDER BY ... DESC, and this is the first time I've seen someone 
respond to say that it's fixed in 4.0.  That's not really evidence, of 
course, but I'm left wondering what the point of implementing INDEX (colname 
DESC) is.

In any case, perhaps you could explain the following result:
SHOW CREATE TABLE inits;
CREATE TABLE `inits` (
  `id` int(11) NOT NULL auto_increment,
  `init` char(1) default NULL,
  PRIMARY KEY  (`id`),
  KEY `init_idx` (`init`)
) TYPE=MyISAM
Table inits has 50,000 rows of randomly distributed characters.
SHOW INDEX FROM inits;
+-------+------------+----------+--------------+-------------+-----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | 
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+
| inits |          0 | PRIMARY  |            1 | id          | A         |
      50000 |     NULL | NULL   |      | BTREE      |         |
| inits |          1 | init_idx |            1 | init        | A         | 

         26 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+
SELECT init FROM inits GROUP BY init ORDER BY init;
+------+
| init |
+------+
| A    |
| B    |
| C    |
...
| X    |
| Y    |
| Z    |
+------+
26 rows in set (0.39 sec)
SELECT init FROM inits GROUP BY init ORDER BY init DESC;
+------+
| init |
+------+
| Z    |
| Y    |
| X    |
...
| C    |
| B    |
| A    |
+------+
26 rows in set (4.09 sec)
This is with mysql 4.0.20.  As you can see, SHOW INDEX says the index on 
init is not packed, but DESC ordering takes 10 times as long.  Explain, in 
case you are wondering, indicates the index on init is being used in both cases.

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