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 To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
