MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query Optimization
Posted by: Bill Karwin
Date: April 26, 2006 04:46PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
3733
April 26, 2006 01:57PM
Re: Query Optimization
2008
April 26, 2006 04:46PM
1905
April 26, 2006 04:56PM
2091
April 26, 2006 05:29PM
1898
May 03, 2006 03:11PM
1891
May 03, 2006 03:31PM


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.