From: Michael Widenius Date: July 16 2009 5:34pm Subject: RE: help with index_merge and clustering keys List-Archive: http://lists.mysql.com/internals/37198 Message-Id: <19039.25786.346737.932523@narttu.askmonty.org> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi! >>>>> "Rick" == Rick James writes: Rick> Let's look at "select * from foo where a=1 and b = 1" in multiple contexts: Rick> (I am assuming there an index containing both a and b.) Rick> * Neither a nor b is clustered: index_merge could be the most efficient way to perform the query... Find all PKs for a=1, find all PKs for b=1, take the intersection; then fetch the rows. Rick> * One of a or b is clustered: As mentioned, use the clustered key only. The other key buys nothing. This is true for the given query, but not in general. Assume you have in an InnoDB table and an index on (b,c) and 'a' is the primary key. In this case: select c from foo where a=1 and b = 1 I't better to scan the index (b,c) than scan the primary key as you get all key parts from this index, but it's much less to scan as you don't have to access all the other columns from the table. Rick> * Both a and b are clustered: Again, the other key is useless; simply use the clustered key. How can you have something clustered on to different key? (Normally you can only cluster on one key at a time) Regards, Monty