An index is not just putting the data in a separate file. It's about storing
information in more "high-tech" data structures like B-trees, R-trees and
hashes. MySQL only support B-Trees, which is the most commonly used. B-Trees
are about storing data in a tree-like structure for very fast retrieval on
slow media, like hard disks. The penalty is a bit slower insertion of data.
But the results are extremely good when searching.
In MySQL having a table that looks like this:
id int primary key
name char(200)
and making an index on name, will get you a 0.1 * original_retrieval_time
when searching, but 1.5*original_insertion_time when inserting. It roughly
means that (on my system that is) you should do at least one select
statement for every 4000 inset statements, for the index to be profitable.
So, keep in mind that an index wont allways suit you, but mostly.
Daniel Åkerud.
> Quoting Cal Evans <[EMAIL PROTECTED]>:
> > Regular keys are just indexes, not necessarily unique, not necessarily
on
> > fields that don't accept nulls. Their primary function is to speed up
data
> > retrieval. Use them sparingly as they can have a negative impact on
> > inserting and updating records.
>
> I understand that indexes work so fast because they are usually smaller
than the
> original table, since they contain on average just 1 column, or at least
less
> information than the complete table. But how about a table with just 2
columns.
> Would an index based on the same 2 columns speed up processing, more
specific,
> would it speed up a SELECT WHERE (the WHERE clause being related to the
index
> algoritm)
>
> Thanks, Marco
>
> > ----- Original Message -----
> > From: "Marco Bleeker" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Friday, June 22, 2001 9:36 AM
> > Subject: KEY and index
> >
> >
> > > Hello, I am learning MySQL and have a question. I know about the
> > importance
> > > of the PRIMARY KEY statement, but I also see a KEY statement being
used
> > > when creating a table. Is this just short for PRIMARY KEY, or what's
the
> > > difference?
> > >
> > > Second question, when I have a large table, but with only 2 small
cells
> > per
> > > row (2 columns), is it useful to create a PRIMARY KEY, KEY, or INDEX
for
> > > speed (there is no set relation to another table).
> > >
> > > Third question, when exactly does MySQL use indexes. Is it used when
the
> > > indexed column is part of a (SELECT *) WHERE statement, together with
a
> > > non-indexed column? I just want to check if matching a row is present
or
> > > not, not actually retrieve information ("WHERE ip='$ip' and
> > date>curdate()")
> > >
> > > Thanks, Marco
> > > |
> > > | Marco Bleeker, Amsterdam
> > > | [EMAIL PROTECTED]
> > > | http://www.euronet.nl/users/mbleeker/
> > > |
> > > | Attachments only after prior notice please.
> > > | Don't put me on any kind of mailing list.
> > > |
> > > | I am now receiving the Snowhite virus 4x a day
> > > | - some of you must be infected, please check !
> > > | (No, you did not get it from me, I use Eudora)
> > > | __@
> > > | _`\<,_
> > > |__(*)/ (*)________________Ah, op DIE fiets !
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
