From: Simon Kimber (simoninternetstuff.ltd.uk)
Date: Tue Aug 18 2009 - 04:08:57 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Hi Everyone,
 
I'm having a very simple query often take several seconds to run and
would be hugely grateful for any advice on how i might spped this up.
 
The table contains around 500k rows and the structure is as follows:
 
+-----------+--------------+------+-----+-------------------+-----------
-----+
| Field | Type | Null | Key | Default | Extra
|
+-----------+--------------+------+-----+-------------------+-----------
-----+
| ID | int(11) | | PRI | NULL |
auto_increment |
| siteid | int(11) | | MUL | 0 |
|
| sender | varchar(255) | | | |
|
| subject | varchar(255) | | MUL | |
|
| message | text | | | |
|
| datestamp | timestamp | YES | MUL | CURRENT_TIMESTAMP |
|
| msgtype | int(1) | | MUL | 0 |
|
| isread | int(1) | | | 0 |
|
+-----------+--------------+------+-----+-------------------+-----------
-----+

I have indexes on siteid, datestamp and msgtype.

Queries such as the following are constantly appearing in the slow
queries log:

SELECT * FROM enquiries WHERE siteid = 59255 AND msgtype = 0 ORDER BY
datestamp DESC LIMIT 5;

An EXPLAIN on the above query returns:

+----+-------------+-----------+------+----------------+--------+-------
--+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+-----------+------+----------------+--------+-------
--+-------+------+-----------------------------+
| 1 | SIMPLE | enquiries | ref | siteid,msgtype | siteid |
4 | const | 1940 | Using where; Using filesort |
+----+-------------+-----------+------+----------------+--------+-------
--+-------+------+-----------------------------+

Shouldn't MySQL be using the datestamp index for sorting the records?
When I remove the ORDER BY clause the query is considerably faster. Do
I need to do something to make sure it using the index when sorting?

Any help will be greatly appreciated!

Regards

Simon

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]