List:Internals« Previous MessageNext Message »
From:Rick James Date:July 16 2009 6:21pm
Subject:RE: help with index_merge and clustering keys
View as plain text  
(below)
 
Rick James
MySQL Geeks - Consulting & Review

> From: Michael Widenius [mailto:monty@stripped] 
...
> 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.

True.  I was assuming the indexes were limited to clustered indexes on
just one key each.
 
> 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)

The thread started by proposing the radical approach of having multiple
clustered indexes.  This is a departure from all(?) existing engines.
The implementation is effectively multiple copies of the entire dataset,
just sorted in different ways.

A simple mapping table illustrates it:
CREATE TABLE map ( a int ..., b int...,  # just the 2 fields
  PRIMARY KEY (a),
  UNIQUE (b)    # (unique is usually desired for a 'mapping')
) ENGINE = InnoDB;   # effectively 2 clustered indexes in InnoDB

To get the equivalent in MyISAM takes:
CREATE TABLE map ( a int ..., b int...,  # just the 2 fields
  PRIMARY KEY (a), # This exists only as a constraint
  UNIQUE (b),      # This exists only as a constraint
  INDEX (a, b),    # The index is "clustered" on a (actually a+b)
  INDEX (b, a)     # clustered on b
) ENGINE = MyISAM;    # had to add extra indexes to get same effect in
MyISAM

Which is a prime example of the need for 
  UNIQUE (a ; b)
(or some other syntax) meaning that UNIQUE applies only to (a), but the
index contains (a,b) to facilitate "Using index".

> Regards,
> Monty
> 
Thread
help with index_merge and clustering keysZardosht Kasheff23 Jun
  • Re: help with index_merge and clustering keysZardosht Kasheff9 Jul
    • Re: help with index_merge and clustering keysSergey Petrunya10 Jul
      • Re: help with index_merge and clustering keysZardosht Kasheff10 Jul
        • RE: help with index_merge and clustering keysRick James10 Jul
          • RE: help with index_merge and clustering keysMichael Widenius16 Jul
            • Re: help with index_merge and clustering keysZardosht Kasheff16 Jul
            • RE: help with index_merge and clustering keysRick James16 Jul
              • RE: help with index_merge and clustering keysMichael Widenius19 Jul
        • Re: help with index_merge and clustering keysSergey Petrunya12 Jul
          • Re: help with index_merge and clustering keysZardosht Kasheff12 Jul
            • Re: help with index_merge and clustering keysSergey Petrunya15 Jul
              • RE: help with index_merge and clustering keysRick James15 Jul
              • Re: help with index_merge and clustering keysZardosht Kasheff24 Jul
      • Re: help with index_merge and clustering keysMichael Widenius16 Jul
Re: help with index_merge and clustering keysZardosht Kasheff10 Jul
Re: help with index_merge and clustering keysZardosht Kasheff16 Jul
  • RE: help with index_merge and clustering keysRick James16 Jul
    • Re: help with index_merge and clustering keysZardosht Kasheff16 Jul