Re: SELECT DISTINCT.. ORDER BY.. DESC - bug??

2004-03-31 Thread Vadim P. 
After a bit of digging, found out that this is indeed a bug:

http://bugs.mysql.com/bug.php?id=1274

Strangely enough, it is listed as CLOSED. Now - this is hard to 
explain, but it looks like MySQL developers have no answer or interest 
in dealing with this problem.

The fact is - adding an index improves SELECT .. ORDER BY .. ASC, but 
slows down SELECT.. ORDER BY .. DESC at least by a factor of 10. If this 
is not a bug, what is it?



Vadim P. wrote:

Hi all,

Sorry to be so persistent, but I am bringing this up again since noone 
from the MySQL development team commented on my previous post, and the 
issue seems very serious, to the point I may start looking to 
switching away from MySQL, so - please, please, shed some light on 
this issue!!!

The problem is that the performance of SELECT DISTINCT... query 
seems to depend on the order the results are sorted, DESC being more 
than 10x slower than ASC (14.77 sec vs. 1.06 sec).

==

Here is a more detailed description:

The table has over 700,000 records. 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)

===

Thanks in advance!
Vadim.


mysql
query






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

SELECT DISTINCT.. ORDER BY.. DESC - bug??

2004-03-30 Thread Vadim P. 
Hi all,

Sorry to be so persistent, but I am bringing this up again since noone 
from the MySQL development team commented on my previous post, and the 
issue seems very serious, to the point I may start looking to switching 
away from MySQL, so - please, please, shed some light on this issue!!!

The problem is that the performance of SELECT DISTINCT... query seems 
to depend on the order the results are sorted, DESC being more than 
10x slower than ASC (14.77 sec vs. 1.06 sec).

==

Here is a more detailed description:

The table has over 700,000 records. 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)
===

Thanks in advance!
Vadim.


mysql
query




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