NYCPHP Meetup

NYPHP.org

[nycphp-talk] Table Indexes

Hans Zaunere lists at zaunere.com
Sun Sep 28 21:18:50 EDT 2008


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

Yes - but be careful with the use of the term "fast" - it'll make MySQL "use
the index".  If you only have a handful of rows, though, a table scan will
be "faster" :)

And that said, the optimizer in MySQL isn't known for its, duh,
intelligence.  Sometimes you'll need to force an index to be used - thus,
always check things with EXPLAIN.

H





More information about the talk mailing list