From: Perrin Harkins
Date: August 18 2009 2:04pm
Subject: Re: Simple query slow on large table
List-Archive: http://lists.mysql.com/mysql/218426
Message-Id: <66887a3d0908180704g67c43cabw1f877855dd5384da@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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