On Mon, Jul 27, 2009 at 9:25 PM, Ken D'Ambrosio <ken@stripped> wrote:
> Hey, all. I'm trying to "get" indexing -- like, when do you specify an
> index name during index creation, is index use implicit or explicit, and,
> honestly, how exactly does it work, anyway? I've been RTFM'ing, but
> haven't found anything that really laid it out in black and white;
> usually, they'd give an example or two, but were awfully sparse on the
> "whys and wherefores."
> So, if anyone has something they could point me to -- electronic or dead
> tree -- I'd be deeply appreciative.
Indexes can be on a single column or can span multiple columns (just like
keys). An index will be used when running a query, if the search is being
performed on the following:
- A single column that has a single-column index for example, if we index
departments on departmentID and perform a query like SELECT...WHERE
- A set of columns that forms a multicolumn index for example, if we have
created an index on the employee.assignment table on (clientID, employeeID,
workdate) and we perform a query like SELECT...WHERE clientID=x AND
employeeID=y AND workdate=z.
- A column or set of columns that forms a subset of a multicolumn index, as
long as there is a leftmost prefix of the index columns for example, with
the assignment table as before, with an index on (clientID, employeeID,
workdate), indexes would be used for these types of queries:
Source: MySQL Tutorial - SAMS Publishing