NYCPHP Meetup

[Michael B Allen]

On Sun, Sep 28, 2008 at 3:02 PM, Hans Zaunere <lists at zaunere.com> wrote:
>> > So is the first column of a primary key always also an index? I
>> > thought I read somewhere that the columns are concatenated together to
>> > form one index?
>>
>> I don't think they're actually concatenated.  The manual says:
>>
>>    A multiple-column index can be _considered_ a sorted array
>>    containing values that are created by concatenating the values
>>    of the indexed columns.
>>
>> Emphasis on "considered" is mine.  However MySQL stores it, the engine
>> has the ability to utilize it.  More information (and the quote above)
>> are from
>> http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html
>
> It's all about what's available left-to-right.  MySQL can't - still AFAIK -
> use parts of an index that are not sequential when using it left-to-right.
> See:
>
> http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
>
> And use EXPLAIN a lot.

I think I'm starting to understand this.

So if I do:

  PRIMARY KEY (a, b),
  INDEX (b)

the PRIMARY KEY (a, b) index makes WHERE with a or a and b fast. The
INDEX(b) is used for a WHERE with only b.

Mike

-- 
Michael B Allen
PHP Active Directory SPNEGO SSO
http://www.ioplex.com/