List:General Discussion« Previous MessageNext Message »
From:muhammad subair Date:July 28 2009 1:45am
Subject:Re: Intro to indexing?
View as plain text  
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.
>
> Thanks!
>

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
departmentID=n.
- 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

-- 
Muhammad Subair

Thread
Intro to indexing?Ken D'Ambrosio27 Jul
  • Re: Intro to indexing?muhammad subair28 Jul
    • Re: Intro to indexing?william drescher29 Jul