Understanding indexes -- detailed information?

[rickowen]

I am looking for detailed information on using indexes in Access. I would
like to know things like when indexes are used (i.e. when the query analyzer
determines to use an index), if the order of columns in a query affect the
use of indexes, how to analyze a query to determine if it will force a query
to be used, etc. I have searched, but all I find are "to create an index in
Access, blah, blah, blah..." or "An index is blah, blah, blah".

I am using a rather large Access database as a back end to a web-based
application and because of constraints imposed upon me I have no alternative.
I must have detailed information about indexes so that I can wring every ms
(millisecond, not Microsoft!) out of each query. Can anyone direct me to
such a source? Online is preferable, but if I have to purchase a book I will.

Thanks,
Rick.

 

[Roger Carlson]

The best source I've found for understanding indexes is the Access
Developer's Handbook by Sybex.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Allen Browne]

Rick Microsoft does not publish details of the index system in Access and
how it works.

Back in the 1980s, a small family business developed a dBase III clone named
FoxBase. It became famous because they developed indexing algorithms that
blew everything else out of the water, and they patented these under the
name Rushmore. In the early 1990s, Microsoft bought out Fox, and they
incorporated Rushmore into version 2 of Access. But they never published the
trade secrets they had purchased.

If you are interested in experimenting, there is a way to get JET to show
you its query plan:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacbk02/html/odc_4009c15.asp
http://builder.com.com/5100-6388-5064388.html
http://msdn.microsoft.com/archive/d...MicrosoftAccessMicrosoftJetDatabaseEngine.asp