Why does adding indexes slow down queries?

[Steeve Richard]

I have a DB that performs aircraft maintenance data
analysis.

It runs dozens of very complex queries on roughly 700 000
records. I used the performance analyzer to determine
which fields were the best canditates for indexation. The
fields suggested made perfect sense because I did a lot of
joins and criteria on them.

Much to my surprize, after creating the suggested indexes,
the execution time of the queries nearly doubled, even
after a compact/repair operation.

What gives?

Steeve

 

[david epsom dot com dot au]

The query planner is not perfect. It may be that using
the index doubles the effort (first read the index, then
read the data), or, more likely, the query planner has
decided to use the indexes to join two large tables
(to reduce the record set by joining the two tables),
before applying some other criteria,
where before it was applying the criteria (to reduce the
recordset) before doing a small join without the indexes

Access does not give you direct control over the order
of processes in a complex query: the only thing you can
do is add/remove indexes and move the WHERE criteria
around in your sub-queries.

(david)