A question about indexing

  • Thread starter Thread starter Richard
  • Start date Start date
R

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
 
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".
Click to expand...

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?
Click to expand...

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.
 
Hi John

Thanks for your help.

Richard

John Vinson said:
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.
Click to expand...
 
Back
Top