muhammad subair wrote:
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
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
