[thelist] SQL Indexes

[Simon Davies]

Indexes:

There are two kinds of indexes both of which can contain unique/non-unique
data:

1. Clustered. A table only allows one clustered index. A clustered index
sorts the table as per your database's defaults (ascending, alphanumeric,
etc) as each new record is added or existing records amended. Therefore,
when retrieving from the table, the data is already sorted thus making your
stored procedure's showplan that much quicker. A table can't have more than
one clustered index because you can't have more than one sort order on a
table. Use a clustered index when searching on columns over a range of
values, eg dates. As records in a clustered index are stored next to each
other, its very quick to retrieve a range of dates, etc. Clustered indexes
are also great for finding a record with a unique id for the same reason.
Don't use clustered indexes on columns that are frequently updated, as the
index will be recalculated each time.

2. Non clustered. Number per table limited by the database you are building
in. Data is stored in the table in the order entered, and found via an index
which points to its location in the table. A non clustered index does not
sort the data, but the index is sorted. Use non clustered indexes for small
result sets, searches on exact matches rather than ranges columns involved
in join operations over multiple tables.

Commonly a table has more than one index. For example,

Table Employee
ID int
Lastname varchar(50)
Firstname varchar(50)
Job Title varchar(50)
Salary money
Date Employed datetime

An example index plan would be:

ID: Primary key, unique and clustered by default of being the primary key
Lastname: non clustered, non unique
Date Employed: non clustered, non unique

Simon Davies





-----Original Message-----
From: thelist-admin at lists.evolt.org
[mailto:thelist-admin at lists.evolt.org]On Behalf Of Scott Dexter
Sent: Friday, 13 October 2000 23:52
To: 'thelist at lists.evolt.org'
Subject: RE: [thelist] SQL Indexes


I'm waiting to see what poetry Rudy has for us on this one. Hold on kids, if
he's in the mood, it'll be a doosy =)

sgd
--
work: http://www.ti3.com/
non: http://thinksafely.org/

> -----Original Message-----
> From: Rob Keniger [mailto:rob at bigbang.net.au]

> > So what would be useful would be some general discussion of
> what indexes are
> > and how one uses them in general - then I can go to the
> MySQL documentation
> > and look up specific implementation.
>
> Indexes are pre-sorted look-up tables of specific columns in
> your database
> that are used to speed record search times.
>

---------------------------------------
For unsubscribe and other options, including
the Tip Harvester and archive of TheList go to:
http://lists.evolt.org Workers of the Web, evolt !