Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Indexes on Yes/No fields (2000)

    Is there anything to be gained by indexing Yes/No fields? I am trying to improve the performance of a contacts database that runs very slowly. The man purpose of the database is to select contacts for mail merges and pretty much every field in the main contacts table is used when creating filters. I have therefore added a lot of indexes. I have now reached the upper limit and I am looking for things to prune.

    Is there a downside to having lots of indexes. I've never reached the upper limit before. I don't think the extra time required to add and update is noticeable (at least not on my test system) but I wondered if the indexes might start operating less efficiently after they reached a critical mass.

    Ian

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Indexes on Yes/No fields (2000)

    An index takes up space (it is in fact an invisible table), and if used unnecessarily, hampers performance. An index on a Yes/No field has no advantages: there are only two values, each of which will occur many times, so sorting or searching does not really gain from the index. So I would remove all indexes on Yes/No fields.

    You do need an index if you have relationships between tables with enforced relational integrity. And you also need an index if you join tables in a query, and want it to be updatable. In general, I tend to define only the required indexes; I don't let Access create indexes based on the field name (the AutoIndex on Import/Create setting in the Tables/Queries tab of Tools | Options... is blank)

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Indexes on Yes/No fields (2000)

    Ian,

    The general rule of thumb is to index fields that you commonly use as selection criteria and/or in sort sequences. That is, above and beyond those required to join tables. One thing to check is to make sure you don't duplicate indexes. Access creates a hidden index on the "many" table in a one-to-many relationship with enforced RI, so you don't want another index on the same field(s). Another thing to look at in what indexes to prune involves fields that are commonly used together. For example, FirstName and Lastname; you might have each one indexed, but maybe you could get by with a single FullName index containing the LastName and FirstName fields (in that order).

    I'm not sure how useful an index on a yes/no field would be. It might be useful in those cases where it was essentially the only selection criteria; but other than that, the nature of how indexes work (of which I only vaguely understand) might render them of little use.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •