List:General Discussion« Previous MessageNext Message »
From:Simon Kimber Date:August 18 2009 9:08am
Subject:Simple query slow on large table
View as plain text  
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
Thread
Simple query slow on large tableSimon Kimber18 Aug
  • Re: Simple query slow on large tablewalter harms18 Aug
  • Re: Simple query slow on large tablePerrin Harkins18 Aug