On Mon, Jul 27, 2009 at 9:25 PM, Ken D'Ambrosio <k...@jots.org> 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
