[nycphp-talk] Table Indexes
Michael B Allen
ioplex at gmail.com
Sun Sep 28 17:11:06 EDT 2008
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/
More information about the talk
mailing list