Re: Query Optimization

[Bill Karwin]

Here's my telephone book analogy for explaining compound indexes:

Find me all people in the phone book whose first name is "Joe".
The phone book is indexed by (last_name, first_name).
So why do I need to scan every page to find all the Joe's?

Answer: searching by part of a compound index _not_ including the leftmost part can't gain any benefit from using the index.

In your query, you reference only the second part of the idx_main index. So it can't use that index.

Regards,
Bill K.