List:General Discussion« Previous MessageNext Message »
From:william drescher Date:July 28 2009 9:12am
Subject: Re: Intro to indexing?
View as plain text  
muhammad subair wrote:
> 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
> 


When you set up the table, you must have a primary index.
You make your best guess as to what would make it easier for the 
sql engine to find the data you want, but the sql engine, in its 
own wisdom will decide whether or not to use an index.  You do 
not explicitly tell it to do a select using an index.

Considerations:
   every time you do an insert, replace, or update of data 
included in an index, the index needs to be updated - which takes 
a small amount of time.
   indexes take space on disk - usually not a problem.
   if the engine can use an index, finding is much faster.
   if the database is small, who cares.

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