Re: "ORDER DESC" vs. "ORDER ASC" exec time

[Vadim P.]

This is correct, the index is not packed. Also, exactly the same 
behavior can be observed if "GROUP BY" is used instead of "DISTINCT" - 
if this makes my case any clearer. The table gets checked for 
consistensy and optimized every hour.

I wonder if any members of MySQL development crew could shed some light 
on the issue?

Just in case someone can catch an obvious mistake, here is the server 
portion of /etc/my.cnf:

[mysqld]
key_buffer_size = 32M
max_allowed_packet = 8M
table_cache = 128
sort_buffer_size = 4M
read_buffer_size = 1M
net_buffer_length = 32K
myisam_sort_buffer_size = 4M
max_connections = 200
set-variable = thread_stack=256k
--------------------------------------------------------------------

Matt W wrote:

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]