List:Internals« Previous MessageNext Message »
From:Rick James Date:July 16 2009 7:26pm
Subject:RE: help with index_merge and clustering keys
View as plain text  
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