List:General Discussion« Previous MessageNext Message »
From:Perrin Harkins Date:August 18 2009 2:04pm
Subject:Re: Simple query slow on large table
View as plain text  
On Tue, Aug 18, 2009 at 5:08 AM, Simon Kimber<simon@stripped> wrote:
> 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;

Read the explanation of ORDER BY optimization:
http://dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html

As it explains, you aren't providing a key it can use.  If you create
a multi-column index on siteid, msgtype, and datestamp, that will
probably fix it.

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