A question about indexing

[Richard]

Hi

If I have a primary table with one of the fields named "contacttype" and a
table ContactType with ContactTypeId and TypeName.

I indexed the field "contacttype" and "ContactTypeId".

If I need to sort or lookup info in the TypeName from a query of the primary
table, should I index TypeName too?

Thanks in advance
Richard

 

[John Vinson]

Hi

If I have a primary table with one of the fields named "contacttype" and a
table ContactType with ContactTypeId and TypeName.

I indexed the field "contacttype" and "ContactTypeId".

If you make ContactTypeID the Primary Key, and link it to ContactType
in your main table with a Relationship (relational integrity
enforced), Access will create the necessary indexes automatically.
Check the Indexes collection of the table - open the tables in design
view and click the Indexes icon; if there are two indexes on these
fields, you can delete the one you created. It's not needed and will
just take up space and slow updates!

If I need to sort or lookup info in the TypeName from a query of the primary
table, should I index TypeName too?

It's not obligatory; in fact if there are not very many (say less than
20, at a wild guess) records in the table a full table scan will be
just as fast as an indexed search, and therefore may not make any
performance difference at all.

 

[Richard]

Hi John

Thanks for your help.

Richard

If you make ContactTypeID the Primary Key, and link it to ContactType
in your main table with a Relationship (relational integrity
enforced), Access will create the necessary indexes automatically.
Check the Indexes collection of the table - open the tables in design
view and click the Indexes icon; if there are two indexes on these
fields, you can delete the one you created. It's not needed and will
just take up space and slow updates!


It's not obligatory; in fact if there are not very many (say less than
20, at a wild guess) records in the table a full table scan will be
just as fast as an indexed search, and therefore may not make any
performance difference at all.