MySQL Forums
Forum List  »  Optimizer & Parser

Re: Not using index optimal
Posted by: Rick James
Date: May 15, 2011 11:25PM

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)
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
Re: Not using index optimal
1117
May 15, 2011 11:25PM
1084
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.