Indexed columns when joining query with query ???

[Dorian]

The main search page of this application uses a query(for its listbox)
containing one right join and seven left joins - most joins are queries to
other queries.
My question is: when a table column is indexed, do the queries that use this
table column also use the indexing in joins?
This is Access 2003.

 

[John Spencer]

If there is an index on a field it will get used in most cases.
Sometimes the db engine will develop a query plan and decide that it is
"cheaper" to scan the file then use the index. HOWEVER, not using an
index is a rare occurence.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[david]

The query plan is created based on the whole stack of queries,
not just the top one.

So the query plan is based on the tables, as if you had put them
all into one big query.

Regarding a specific query, there is no absolute way to predict
if an index will be used for a join, or if the engine will decide to
just scan the second table for matching records. The optimiser
makes a decision based on the data statistics for the tables.
If the records are unique, it shouldn't make much difference,
and the optimiser may decide to use a table scan instead of
an index join.

(david)

 

[Jerry Whittle]

Do a google search on ShowPlan. It's a free tool from MS which will tell you
how a query was run including which indexes were used.