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
>> >>
>> >
>>
>
