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

Written By
May 14, 2011 01:02PM
May 14, 2011 01:33PM
May 15, 2011 01:57PM
May 16, 2011 03:23AM
May 16, 2011 06:41AM
Re: Not using index optimal
May 15, 2011 11:25PM
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.