From: Zardosht Kasheff
Date: July 16 2009 7:32pm
Subject: Re: help with index_merge and clustering keys
List-Archive: http://lists.mysql.com/internals/37202
Message-Id: <2f9663ba0907161232m3a4861fjf6855955695e1674@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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? =A0Seems like you c=
ould 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..., =A0# just the 2 fields
>> > =A0PRIMARY KEY (a),
>> > =A0UNIQUE (b) =A0 =A0# (unique is usually desired for a 'mapping')
>> > ) ENGINE =3D InnoDB; =A0 # effectively 2 clustered indexes in InnoDB
>> >
>> > To get the equivalent in MyISAM takes:
>> > CREATE TABLE map ( a int ..., b int..., =A0# just the 2 fields
>> > =A0PRIMARY KEY (a), # This exists only as a constraint
>> > =A0UNIQUE (b), =A0 =A0 =A0# This exists only as a constraint
>> > =A0INDEX (a, b), =A0 =A0# The index is "clustered" on a (actually a+b)
>> > =A0INDEX (b, a) =A0 =A0 # clustered on b
>> > ) ENGINE =3D MyISAM; =A0 =A0# had to add extra indexes to get
>> same effect in
>> > MyISAM
>> >
>> > Which is a prime example of the need for
>> > =A0UNIQUE (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
>> >>
>> >
>>
>