Re: Not using index optimal
Rick James Wrote:
-------------------------------------------------------
> No, that is a poor index for that query. It's
> like asking for all people with first name 'Per',
> but the index starts with last name.
>
> WHERE a=3 AND b='ACTIVE' ORDER BY c, pid LIMIT 0,
> 100
> Construct the optimal index thus:
> 1. include the '=' parts of the WHERE, a and b, in
> either order.
> 2. add in the ORDER BY items (c, pid) in the same
> order.
> So, optimal is either of these:
> INDEX(a, b, c, pid)
I follow you explanations and agree all the way. This is exactly the index that I have. Dont know what index you thought I had?
> INDEX(b, a, c, pid)
>
> (Of course, other SELECTs won't follow that
> pattern exactly.)