[Last Call] Learn how to a build a cloud-first strategyRegister Now


how do ms access indexes work

Posted on 2014-07-16
Medium Priority
Last Modified: 2014-07-17
Just what to how what method does Access use to retrieve records. That is, read entire index first then retrieve related records.  And how

Question by:danlopez007
LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 1200 total points
ID: 40200310
It's complicated.   JET has a number of options depending on the types of indexes, fields involved, etc.

In general, it uses indexes when it can, which are ISAM indexes.   It uses some (but not all) of the Rushmore optimizations from Visual Fox Pro.  Indexes may be intersected, Unioned, or counted and compared.  When performing joins between tables, there are five different join strategies that can be used.

 Resorting to a scan of the table (reading the data records) is always a last resort (and not always the case as some believe).

To see the costing plan that JET/ACE uses, you need to turn on JETSHOW plan.   With SHOWPLAN on, a text output file will be created when JET executes a query.   It will not revel the costing plan of any nested or sub queries.

LVL 13

Assisted Solution

magarity earned 300 total points
ID: 40200340
In general, the answer to your part about reading the entire index, the answer is usually no.  any query that causes the system to read every index entry would then read every table entry and that would be inefficient.  if it cannot efficiently seek down to a few entries in the index relative to the table, it will usually just skip the index and go read the whole table.

Edited by JDettman - Link policy.
LVL 75
ID: 40200378
Further ....

Note that indexes can be a double edge sword. Whereas they will (usually) most definitely speed up searches and filtering - almost like magic sometimes,  each time you add or delete a record, all indexes in the table have to be updated by Jet. So, if you have several hundred thousand records, then the updating time could be significant, depending on various aspects of your system and/or network connection is there is one, etc.

Indexing can be somewhat of an art form.  The bottom line is be judicious in your use of indexes. Don't just randomly index fields for no reason. In addition, test the effectiveness of your indexes on test (or live) datasets that represent the amount of data you expect to encounter ... and see for yourself how they affect performance, good or bad.

LVL 20

Expert Comment

ID: 40201696
What is the reason for your question?
Are you searching for a way to speed up your queries?

Typically, we don't worry too much about how the index works.  We just know the "good rules" to apply (don't index on everything, use numbers for primary keys and such, avoid index of long text fields, etc.)

Scott C

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question