List:Internals« Previous MessageNext Message »
From:Zardosht Kasheff Date:July 16 2009 7:32pm
Subject:Re: help with index_merge and clustering keys
View as plain text  
At the moment, yes, you need to add another index. It should not be
too hard to add it.

On Thu, Jul 16, 2009 at 3:26 PM, Rick James<rjames@stripped> wrote:
> So, if I want UNIQUE(b), I need to add another index?  Seems like you could
> provide that feature.
>
>
> Rick James
> MySQL Geeks - Consulting & Review
>
>
>
>> -----Original Message-----
>> From: Zardosht Kasheff [mailto:zardosht@stripped]
>> Sent: Thursday, July 16, 2009 11:35 AM
>> To: Rick James
>> Cc: monty@stripped; Sergey Petrunya;
>> internals@stripped; serg@stripped
>> Subject: Re: help with index_merge and clustering keys
>>
>> 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