List:Internals« Previous MessageNext Message »
From:Zardosht Kasheff Date:July 16 2009 6:35pm
Subject:Re: help with index_merge and clustering keys
View as plain text  
Minor thing. Our implementation of clustering keys do not ensure
uniqueness. So in the examples below in the mapping tables, the key b
does not have uniqueness enforced.

So, if one does "create table foo (a int, b int, c int, ..., primary
key (a), clustering key (b));", 'b' may have multiple values that are
the same.

> 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