Hi, MySQL 4+ can use indexes for ORDER BY ... DESC (3.23 can only in some cases) in every case that ASC can. However, reading a packed index in reverse order is slower. I don't think your index is packed, though, if it's a date-type column, unless you've specified PACK_KEYS in your CREATE TABLE. Better check with SHOW INDEX FROM PhoneCalls.
That leaves the DISTINCT clause as the suspect. The EXPLAINs look the same with ASC/DESC on an equivalent query I just tried. So maybe something is making DISTINCT + reverse index scan slow even if it's not packed... Matt ----- Original Message ----- From: "Vadim P." Sent: Monday, March 29, 2004 8:15 PM Subject: "ORDER DESC" vs. "ORDER ASC" exec time > Hi all, > > Just noticed that a simple query that returns only 14 rows is 10X slower > when "ORDER .. DESC" is used compared to "ORDER .. ASC". > The table has about 700,000 records, indexed on the field the table is > being ordered by. > > Is this expected behavior? > > MySQL 4.0.18 running under OpenBSD 3.4 Intel/PIII 900MHz/2GB RAM > > ================================= > > mysql> SELECT distinct billingCycle FROM PhoneCalls ORDER BY > billingCycle DESC; > +--------------+ > | billingCycle | > +--------------+ > | 2004-04-01 | > | 2004-03-01 | > | 2004-02-01 | > | 2004-01-01 | > | 2003-12-01 | > | 2003-11-01 | > | 2003-10-01 | > | 2003-09-01 | > | 2003-08-01 | > | 2003-07-01 | > | 2003-06-01 | > | 2003-05-01 | > | 2003-04-01 | > | 2003-01-01 | > +--------------+ > 14 rows in set (14.77 sec) > > mysql> SELECT distinct billingCycle FROM PhoneCalls ORDER BY billingCycle; > +--------------+ > | billingCycle | > +--------------+ > | 2003-01-01 | > | 2003-04-01 | > | 2003-05-01 | > | 2003-06-01 | > | 2003-07-01 | > | 2003-08-01 | > | 2003-09-01 | > | 2003-10-01 | > | 2003-11-01 | > | 2003-12-01 | > | 2004-01-01 | > | 2004-02-01 | > | 2004-03-01 | > | 2004-04-01 | > +--------------+ > 14 rows in set (1.06 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
