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]
