from mysql:
"With EXPLAIN SELECT ... ORDER BY, you can check whether MySQL can use indexes to resolve the query. It cannot if you see Using filesort in the Extra column."
............. further :
"In MySQL 4.1 and up, a filesort optimization is used that records not only the sort key value and row position, but also the columns required for the query. This avoids reading the rows twice."
so suggestion is to upgrade to 4.1+. This is most likely not a viable solution :) so further from their website:
"If you want to increase ORDER BY speed, first see whether you can get MySQL to use indexes rather than an extra sorting phase. If this is not possible, you can try the following strategies:
a.. Increase the size of the sort_buffer_size variable. b.. Increase the size of the read_rnd_buffer_size variable. c.. Change tmpdir to point to a dedicated filesystem with lots of empty space. If you use MySQL 4.1 or later, this option accepts several paths that are used in round-robin fashion. Paths should be separated by colon characters (`:') on Unix and semicolon characters (`;') on Windows, NetWare, and OS/2. You can use this feature to spread the load across several directories. Note: The paths should be for directories in filesystems that are located on different physical disks, not different partitions of the same disk. " also from their site, if I am not mistakin, they suggest to use GROUP BY the colum that will be sorted by (which forces the sort and might eliminate the rereading of the rows) with ORDER BY NULL at the end to eliminate the overhead of sorting (since it will be sorted in 'GROUP BY deletedate DESC') :
SELECT ID FROM Offers_To_Buy WHERE subcatID = 336 GROUP BY deletedate DESC ORDER BY NULL LIMIT 20 ;
if your deletedate is not unique then mesh some already used key into it (ID
or subcatID) to eliminate actual grouping:
SELECT ID FROM Offers_To_Buy WHERE subcatID = 336 GROUP BY deletedate DESC,
ID ORDER BY NULL LIMIT 20 ;
(note to that, you ID field, thought autoincremented, is not set to be
unique, so unless you know it is unique for a fact then meshing it with
deletedate might still produce undesirable grouping. In that case you might
have to add some unique number in group by clause liek a current row
counter, or worst case a random number).
------- It might not speed up the query depending on the size of the WHERE results,
but might not be as random on completion time.
Curiouse if it does anything, let me know if you try.
----- Original Message ----- From: "Aaron" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, October 25, 2004 4:19 PM
Subject: Slow ORDER BY query..
Hi all ,
I am currently experiencing an issue with a query I would have thought to be somewhat straightforward. Perhaps someone could shed some light on what might be causing this? The below example was running by itself , not waiting for any other queries. It just took a bloody long time to run. The system load went to around 7 or so , however the CPU's were not taxed at all.
Of curious note to me , is that it seems to be intermittently taking a long time. Upon restarting of the server and "flushing" the cache , some queries will take < 1 second , some will take around 5 , and some will take ridiculously long times. I think that has to do perhaps with the amount of rows matching before the ORDER BY?
I've included all the information I can think of below if anyone feels like having a look, It would be be greatly appreciated.
Thanks! Aaron
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
MySQL Version: ~~~~~~~~~~ MySQL 4.0.18 on RedHat Linux
The Query
~~~~~~~~
mysql> SELECT ID FROM Offers_To_Buy WHERE subcatID = 336 ORDER BY deletedate DESC LIMIT 20 ;
+---------+
| ID |
+---------+
| 1653497 |
| 1653498 |
| 1653506 |
| 1652861 |
| 1652685 |
| 1652784 |
| 1651739 |
| 1650276 |
| 1650323 |
| 1649569 |
| 1649079 |
| 1649228 |
| 1649410 |
| 1649411 |
| 1648444 |
| 1648543 |
| 1648877 |
| 1648897 |
| 1648911 |
| 1648308 |
+---------+
20 rows in set (2 min 52.20 sec)
Record Count: mysql> SELECT count(1) FROM Offers_To_Buy ; +----------+ | count(1) | +----------+ | 461216 | +----------+ 1 row in set (0.00 sec)
Explain Output:
mysql> EXPLAIN SELECT ID FROM Offers_To_Buy WHERE subcatID = 336 ORDER BY deletedate DESC LIMIT 20 ;
+---------------+------+------------------+-------------+---------+-------+------+-----------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------------+------+------------------+-------------+---------+-------+------+-----------------------------+
| Offers_To_Buy | ref | subcategory,scdd | subcategory | 4 | const | 8562 | Using where; Using filesort |
+---------------+------+------------------+-------------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)
The Table:
~~~~~~~~
mysql> describe Offers_To_Buy ;
+------------------+------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+------------+----------------+
| bid | mediumtext | | MUL | | |
| company_name | varchar(50) | YES | | NULL | |
| street_address | varchar(100) | YES | | NULL | |
| city | varchar(25) | YES | | NULL | |
| province | varchar(25) | YES | | NULL | |
| country | varchar(25) | | MUL | | |
| postal_code | varchar(10) | YES | | NULL | |
| phone | varchar(50) | YES | | NULL | |
| fax | varchar(50) | YES | | NULL | |
| email | varchar(100) | | | | |
| contact_name | varchar(50) | YES | | NULL | |
| keywords | varchar(100) | YES | MUL | NULL | |
| URL | varchar(200) | YES | | NULL | |
| obtain_documents | varchar(50) | YES | | NULL | |
| cost | float(10,2) | YES | | NULL | |
| deletedate | date | | MUL | 0000-00-00 | |
| bidvalue | float(10,2) | YES | | NULL | |
| country_dest | varchar(25) | YES | | NULL | |
| subcatID | int(10) unsigned | | MUL | 0 | |
| ID | int(10) unsigned | | MUL | NULL | auto_increment |
| source | varchar(30) | | MUL | | |
| approved_by | varchar(30) | YES | | NULL | |
| oldtitle | varchar(100) | | MUL | | |
| Closed | tinyint(4) | | | 0 | |
| userID | int(10) unsigned | | MUL | 0 | |
| image | varchar(30) | YES | | NULL | |
| postDate | date | YES | | NULL | |
| blank5 | char(1) | YES | | NULL | |
+------------------+------------------+------+-----+------------+----------------+
28 rows in set (0.00 sec)
Indexes:
mysql> SHOW INDEXES FROM Offers_To_Buy ;
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Offers_To_Buy | 1 | ID | 1 | ID | A | 461216 | NULL | NULL | | BTREE | |
| Offers_To_Buy | 1 | oldtitle | 1 | oldtitle | A | 230608 | NULL | NULL | | BTREE | |
| Offers_To_Buy | 1 | user | 1 | userID | A | 5426 | NULL | NULL | | BTREE | |
| Offers_To_Buy | 1 | date | 1 | deletedate | A | 1314 | NULL | NULL | | BTREE | |
| Offers_To_Buy | 1 | subcategory | 1 | subcatID | A | 1267 | NULL | NULL | | BTREE | |
| Offers_To_Buy | 1 | country | 1 | country | A | 240 | NULL | NULL | | BTREE | |
| Offers_To_Buy | 1 | source | 1 | source | A | 97 | NULL | NULL | | BTREE | |
| Offers_To_Buy | 1 | scdd | 1 | subcatID | A | 1267 | NULL | NULL | | BTREE | |
| Offers_To_Buy | 1 | scdd | 2 | deletedate | A | 230608 | NULL | NULL | | BTREE | |
| Offers_To_Buy | 1 | keywords | 1 | keywords | A | 115304 | NULL | NULL | YES | FULLTEXT | |
| Offers_To_Buy | 1 | bid | 1 | bid | A | 115304 | NULL | NULL | | FULLTEXT | |
+---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
11 rows in set (0.00 sec)
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
