[AccessD] OT...sort of...general db question

John,

<<My question is this: what would be a programmer's reason to omit an index,
for a table? >>

  There are two basic reasons that one might not index:

1. The index would not be unique enough.  This is called cardinality.  A
good example of something that is not very unique is an index on a yes/no
column.  There are only two possible values in the index.  As a result,
looking at the index is not going to be very helpful. Nine times out of ten,
a table scan is going to be just as fast as the index, so the index is just
extra overhead.

2. The more indexes you add the more overhead you have.  Maintaining indexes
takes time and can lead to performance and concurrency issues. 

  A good example if where this would be a problem would be a historical
table.  Because you never know which way the data is going to be looked at,
defining a bunch of indexes is not going to do a lot of good.  Trying to do
so would lead to severe performance and concurrency issues when new data was
added. 

HTH,
Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Clark
Sent: Monday, April 14, 2008 7:56 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] OT...sort of...general db question

Hi all...long time since I've asked for any help, but something came up
today, which makes me curious...
 
We have a program, which uses SQL as its db host. There are a bazillion
tables...an estimate of course...in this thing, and all but one is indexed.
The table that is not indexed is the 3rd most populated table, and it is
several tens of thousands of records.
 
My question is this: what would be a programmer's reason to omit an index,
for a table? 
 
I understand that one cannot speak for this specific program, but I am just
looking for a general answer. It has always been drilled into my head,
"index, index, INDEX!" So, I'm wondering if there are times that I may NOT
want to include an index.
 
Thank you all for your time.
 
John W Clark
 
John W. Clark
Computer Programmer 
Niagara County
Central Data Processing
         
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com