Hello,

I've been reading a bit about database performance optimizations and was hoping you guys could clarify something for me about indexing.

From what i've read so far indexing can often help improve performance time on queries however it can slow inserts/updates down.

Im writing a application that accesses a simple access database table.
I'm not expecting it to be updated more than 5-10 times a day but is likely to be queried constantly throughout the day.

From what i've read it sounds like i should be putting indexes on all my fields in my tables (except teh ones that cant be indexed anyway-memo etc).

When should i NOT put indexes on my fields?
Would it be more beneficial to only put indexes on fields that i'm going to sort by?
Do indexes help when searching a field for specific criteria?

Any help you guys can give me would be appreciated

Thanks

"When should i NOT put indexes on my fields?"

when the field is niether searched on, nor joined, on, nor grouped by, nor ordered by

"Would it be more beneficial to only put indexes on fields that i'm going to sort by?"

see above, re: joins, groups, and searching

"Do indexes help when searching a field for specific criteria?"

oh, you betcha

Generally, indexes are useful on columns that:
a) are fairly selective (i.e. have few rows per value, relative to the total number of rows in the table)
b) are used in the WHERE clause of queries in a selective manner

Primary/unique keys should always be indexed (in fact, you can't avoid indexing them in some DBMSs at least).

Foreign keys are often a good candidate, because they often appear in join conditions. (In Oracle you should index all foreign keys as a rule, for other reasons concerning locking).

Other non-key columns may be indexed if a benefit is likely. For example, an index on employee.surname is probably useful for name-based searches, but an index on employee.salary is unlikely to be very useful (how often do people search for employees where salary = 1000 for example?)

You should read the documentation and books relating to your specific DBMS, because the best indexing strategy may be different for different DBMSs.

Thanks r937 & andrewst
That's pretty much all i need - and then some!

Much Appreciated

Just an added notefrom an old hand: In general after you have created an index on the primary or significant business field, don't create any more indices unless and until they are clearly needed (i.e. performance is bad).

Often even though it looks like an index is required, the database optimiser may not use it - for example if the data volume is small, a full table pass may be faster. (Actually if the data volume is small enough you may not need any indexes.)

This of course means you need to do adequate performance testing before the database goes into production, with suitable volumes of data.