MySQL Forums
Forum List  »  Optimizer & Parser

Re: Not using index optimal
Posted by: Per Steffensen
Date: May 16, 2011 03:18AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
4040
May 14, 2011 01:02PM
1092
May 14, 2011 01:33PM
1225
May 15, 2011 01:57PM
1126
May 16, 2011 03:23AM
1230
May 16, 2011 06:41AM
1117
May 15, 2011 11:25PM
Re: Not using index optimal
1083
May 16, 2011 03:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.