Simon Kimber schrieb: > 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 >
hi Simon, you can try a "join" see http://www.artfulsoftware.com/infotree/queries.php for hints. sql is pretty bad for time series data. IMHO is the most obvious thing to reduce the number entries in your table. (do you realy need ID when you have a timestamp ?, etc) Otherwise the other stuff like: myisam instead of immodb but this depends on your requirements. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
