How to optimize with additional indexes?

[Siegfried Heintze]

I have a table of job postings with an auto increment integer ID for the PK.

I want to define an index (in addition to the PK) to speed up the searches
as it is quite a large table. I often need to get the integer PK given
something else that (hopefully) uniquely defines the job. I fill in as many
field names as the user gives me and only use those in my SQL select
command.

Is this strategy a problem? Would there be merit to specifying all the
fields and leave the ones the user did not fill in as zero length strings or
nulls (which is better)?

I was wondering how I should set up a secondary key to speed up searching
for the PK given the other fields in the WHERE clause. Should I just define
all the other fields as part of a secondary key? Will jet use the composite
secondary index if I only specify, say one field (such as the Job Title)?

I'm rewriting a program that used to process 10 jobs a second using MySQL
and presently my performance with MSAccess 2003 is pretty poor, about .5
jobs a second. I know the old MySQL perl program was very slow until I added
some more indices, but I cannot remember how I did it and if I did, I did
not know which indices to attribute to the speed improvement. (The moral of
the story is, always run benchmarks before adding new indices)!

Thanks,
Siegfried

 

[Jeff Boyce]

A generic approach, which perhaps you've already taken...

If you use a table's field in a join, index it.
If you use a table's field as a selection criterion, index it.
If the table's field has relatively few values (e.g., Gender /Male,

Female, Unknown, Undecided), you probably won't gain any speed by indexing
it.

If the table's fields consists of entirely unique values (e.g., an

autonumber ID field), you won't gain much by indexing it (unless it matches
one of the above criteria).

I'm sure there're more rules of thumb, but that's my start...

Regards

Jeff Boyce
<Access MVP>