Re: Not using index optimal

[Rick James]

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