Multiple field primary key and other indexes

[Eric Heinold]

I have a database that has multiple fields for it's primary. When the table
was created, it had an indexes for each of the 3 fields in the primary key
defined. Since any of these 3 fields are used for sorting, I wondered if it
would cause any problems to remove these extra indexes. I did it as a test
and saw a speed increase for my forms opening, but did not know if there
will be other degradations becuase removing them.

Example:
UserID
ItemID
YearID
Multiple other fields

These three fields are are defined as the primary key. Indexes look like
this:
Primary Key - UserID, ItemID, YearID
UserID
ItemID
YearID

The last 3 indexes are non-unique. I would like to remove them from the
table. When I did this for testing, it made the forms open faster. I could
not find any information on whether the Primary key index is enough to help
speed up sorting and searches on any one of three fields.

Thanks,

Eric Heinold

 

[Tim Ferguson]

sorting, I wondered if it would cause any problems to remove these
extra indexes. I did it as a test and saw a speed increase for my
forms opening, but did not know if there will be other degradations
becuase removing them.

Indexes are good for data retrieval, bad for updates and insertions. I
don't really know why removing the indexes would affect the speed of form
opening; but it would likely help with updating and inserting a record
when moving to the next record.

Unless it's a really big table, though, I'd be surprised if the
difference were noticeable. It may be that removing them caused a table
re-write within the database, and it's that which caused the improved
access. Try compacting the file more often.

I could not find any information on whether the Primary key
index is enough to help speed up sorting and searches on any one of
three fields.

It will effectively index the first field but not the others. I have to
confess that I am notoriously lazy about creating non-constraining
indexes and I don't usually miss them. The short answer is, don't sweat
it.

Hope that helps


Tim F