List:General Discussion« Previous MessageNext Message »
From:walter harms Date:August 18 2009 10:09am
Subject:Re: Simple query slow on large table
View as plain text  

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
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