how do ms access indexes work

Posted on 2014-07-16
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 56

    Accepted Solution

    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

    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

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    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

    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

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Access DLookup 18 22
    Access Database - Manually changing xid value 7 32
    Printing Problem 13 21
    Access query that references subform 5 19
    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now