How do I know which fields I should index and which ones I shouldn't?

How do I know which fields I should index and which ones I shouldn't?



Two possibilities to consider:

The Primary Key is the key that is most often indexed
Keys that are used as Foreign Keys to other tables should be considered as well

Ok good to know. So how would I set a field to not allow duplicates if it isn't indexed?

Ok good to know. So how would I set a field to not allow duplicates if it isn't indexed?

When you create the Table in the Table Designer, there is an option to disallow duplicates for any given field..

MSAccessRookie,

I looked there and the only way I see to do it is Yes (No duplicates) in the index field.

MSAccessRookie,

I looked there and the only way I see to do it is Yes (No duplicates) in the index field.

I am not sure what the answer to that question is. I have never really given it much thought, because I normally Index the fields that have to be DISTINCT, since those are either my Primary Keys, or are used as Foreign Keys.

so would it hurt for me to index a field that is not my primary key or foreign key?

index fields that you are likely to use for search on

an index can combine (and often does) several fields

especially for real world things like sales orders
eg sales lines will almost certainly have orderno/product as a key, and probably product/orderno also

just depends on the table really

so would it hurt for me to index a field that is not my primary key or foreign key?

Indexing One or two probably won't create issues, but going hog-wild and over-indexing is probably not a good idea either. Gemma also makes a great point about compound indexes.