On Fri, 21 Apr 2006, John Newby wrote:
>A little off subject I know, but what is the purpose of an index?
Just like in a book. If you want to search for particular terms in a book,
you look up the page number in the index.
In a database, fields are indexed, so you can see which rows contain
particular field values without searching the whole table. For example,
consider the following rows:
rowid | field1 | field2
------+--------+-------
0 | foo | bar
1 | foo | foo
2 | bar | foo
3 | bar | bar
If the field1 is indexed, then your index will look like
key | rowid
------+------
foo | 0
foo | 1
bar | 2
bar | 3
If we want all rows where field1 = 'foo', then we can use the index to
locate the only two rows that satisfy the condition (0 and 1 in this
case).
If we want to find all rows where field2 = 'foo', then we have to search
the whole of the table.
Indexes come into their own when tables have thousands or millions of rows
with largely disparate field values. Finding a particular row in these
cases can be as quick as a single index lookop followed by a single row
lookup, instead of a multi-million row table scan.
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
