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]
