How to know if # of indeces is too many?

Discussion in 'Access Table Design' started by LAS, Sep 5, 2010.

  1. LAS

    LAS Guest

    I want to make my backend as efficient as possible, so I thought to add
    indexes on any column that might be a parameter in a Where clause, etc.
    When do I have to worry about adding too many indeces?

    TIA
    LAS
     
    LAS, Sep 5, 2010
    #1
    1. Advertisements

  2. On Sun, 5 Sep 2010 17:36:16 -0400, "LAS" <> wrote:

    >I want to make my backend as efficient as possible, so I thought to add
    >indexes on any column that might be a parameter in a Where clause, etc.
    >When do I have to worry about adding too many indeces?


    In an Access table, you'll have a limit of 32 indexes; you don't have to worry
    much because the program will simply refuse to add the 32nd index.

    More subtly, you face a tradeoff of costs and benefits. Additional indexes
    will speed data *retrieval*, but they will slow data *addition* (and changes
    to data); each time you add a new record, or change a value in an indexed
    field, the program must not only add the record to the table, but also to all
    the affected indexes. Since an index is a tree structure, this can be an
    expensive process. In addition, the indexes take up disk and storage space
    (probably not a major issue but if your database is very large, it could be a
    problem).

    I remember years ago on a big Oracle database having to go through some pretty
    extensive testing, long discussions with the DBA, and some extensive analysis
    to figure out which indexes were helpful on balance.

    A lot depends on your table size. With a 5000 row table, the time savings on
    an indexed search vs. a full table scan may be imperceptible to the user; on f
    500,000 row table, it's going to be very obvious!
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Sep 6, 2010
    #2
    1. Advertisements

  3. LAS

    Tony Toews Guest

    On Sun, 05 Sep 2010 17:08:17 -0600, John W. Vinson
    <jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

    >A lot depends on your table size. With a 5000 row table, the time savings on
    >an indexed search vs. a full table scan may be imperceptible to the user; on f
    >500,000 row table, it's going to be very obvious!


    That said a client had an 800K record table and one particular query
    was slow. One of the joins was to a 500 record job table with an
    inactive boolean field. One of the selection criteria was on that
    inactive field.

    Adding an index on that boolean field had the query executing in under
    5 seconds compared to 30 seconds.

    Tony
    --
    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files
    updated see http://www.autofeupdater.com/
     
    Tony Toews, Sep 28, 2010
    #3
  4. On Tue, 28 Sep 2010 15:08:51 -0600, Tony Toews <> wrote:

    >On Sun, 05 Sep 2010 17:08:17 -0600, John W. Vinson
    ><jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
    >
    >>A lot depends on your table size. With a 5000 row table, the time savings on
    >>an indexed search vs. a full table scan may be imperceptible to the user; on f
    >>500,000 row table, it's going to be very obvious!

    >
    >That said a client had an 800K record table and one particular query
    >was slow. One of the joins was to a 500 record job table with an
    >inactive boolean field. One of the selection criteria was on that
    >inactive field.
    >
    >Adding an index on that boolean field had the query executing in under
    >5 seconds compared to 30 seconds.
    >
    >Tony


    Thanks, Tony. That certainly is a good datapoint for the argument about
    indexing Boolean fields!
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Sep 29, 2010
    #4
  5. LAS

    Tony Toews Guest

    On Tue, 28 Sep 2010 17:14:12 -0600, John W. Vinson
    <jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

    >Thanks, Tony. That certainly is a good datapoint for the argument about
    >indexing Boolean fields!


    Where appropriate of course. A join with the largest table at 1000
    records no big deal.

    Tony
    --
    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files
    updated see http://www.autofeupdater.com/
     
    Tony Toews, Sep 29, 2010
    #5
  6. "Tony Toews" <> wrote in message
    news:...
    > On Sun, 05 Sep 2010 17:08:17 -0600, John W. Vinson
    > <jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
    >
    >>A lot depends on your table size. With a 5000 row table, the time savings
    >>on
    >>an indexed search vs. a full table scan may be imperceptible to the user;
    >>on f
    >>500,000 row table, it's going to be very obvious!

    >
    > That said a client had an 800K record table and one particular query
    > was slow. One of the joins was to a 500 record job table with an
    > inactive boolean field. One of the selection criteria was on that
    > inactive field.
    >
    > Adding an index on that boolean field had the query executing in under
    > 5 seconds compared to 30 seconds.


    That's interesting, because I was always told that you shouldn't index
    fields with low cardinality (and only two possible values is probably the
    lowest cardinality you're ever going to see!)

    --
    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/DJSteele/AccessIndex.html
    Co-author: "Access 2010 Solutions", published by Wiley
    (no private e-mails, please)
     
    Douglas J. Steele, Sep 30, 2010
    #6
  7. On Thu, 30 Sep 2010 17:31:10 -0400, "Douglas J. Steele"
    <NOSPAM_djsteele@NOSPAM_gmail.com> wrote:

    >> Adding an index on that boolean field had the query executing in under
    >> 5 seconds compared to 30 seconds.

    >
    >That's interesting, because I was always told that you shouldn't index
    >fields with low cardinality (and only two possible values is probably the
    >lowest cardinality you're ever going to see!)


    I'm sure it depends on the data. If you have a Yes/No field with a million
    records, 100 of which are Yes and the remainder No, and a query including a
    criterion of True on this field (along with other criteria), the index will
    retrieve only 100 records which must be checked. Without an index you must do
    a full table scan.

    If your criterion is False, then I'm guessing it will still retrieve 999,900
    rows and scan them all.
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Sep 30, 2010
    #7
  8. "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in
    news:i82viq$kbp$-september.org:

    > "Tony Toews" <> wrote in message
    > news:...
    >> On Sun, 05 Sep 2010 17:08:17 -0600, John W. Vinson
    >> <jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
    >>
    >>>A lot depends on your table size. With a 5000 row table, the time
    >>>savings on
    >>>an indexed search vs. a full table scan may be imperceptible to
    >>>the user; on f
    >>>500,000 row table, it's going to be very obvious!

    >>
    >> That said a client had an 800K record table and one particular
    >> query was slow. One of the joins was to a 500 record job table
    >> with an inactive boolean field. One of the selection criteria
    >> was on that inactive field.
    >>
    >> Adding an index on that boolean field had the query executing in
    >> under 5 seconds compared to 30 seconds.

    >
    > That's interesting, because I was always told that you shouldn't
    > index fields with low cardinality (and only two possible values is
    > probably the lowest cardinality you're ever going to see!)


    Yes, that's the stereotypical advice.

    And it's simply wrong.

    --
    David W. Fenton http://www.dfenton.com/
    contact via website only http://www.dfenton.com/DFA/
     
    David-W-Fenton, Oct 1, 2010
    #8
  9. LAS

    Allen Browne Guest

    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> replied in
    message news:i82viq$kbp$-september.org...
    > That's interesting, because I was always told that you shouldn't index
    > fields with low cardinality (and only two possible values is probably the
    > lowest cardinality you're ever going to see!)


    Yes, that's what the text books say, Doug.

    I can confirm what others are saying here. If you run some tests, you'll
    find that it is not good advice in JET for a field where you regularly
    filter. My tests on a table of 15k clients with an Inactive field found a
    performance boost of 3 - 4 times by indexing the y/n field.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia

    Reply to group, rather than allenbrowne at mvps dot org.
     
    Allen Browne, Oct 1, 2010
    #9
  10. LAS

    Tony Toews Guest

    On Thu, 30 Sep 2010 17:31:10 -0400, "Douglas J. Steele"
    <NOSPAM_djsteele@NOSPAM_gmail.com> wrote:

    >That's interesting, because I was always told that you shouldn't index
    >fields with low cardinality (and only two possible values is probably the
    >lowest cardinality you're ever going to see!)


    See now this is where
    1) not having a formal education helps. No blinders.
    2) not trusting what anybody has to say about anything except for
    trusted individuals. MVPs as well as David Fenton of course, fall in
    this category.
    <smile>

    Tony
    --
    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files
    updated see http://www.autofeupdater.com/
     
    Tony Toews, Oct 1, 2010
    #10
  11. Tony Toews <> wrote in
    news:p:

    > On Thu, 30 Sep 2010 17:31:10 -0400, "Douglas J. Steele"
    ><NOSPAM_djsteele@NOSPAM_gmail.com> wrote:
    >
    >>That's interesting, because I was always told that you shouldn't
    >>index fields with low cardinality (and only two possible values is
    >>probably the lowest cardinality you're ever going to see!)

    >
    > See now this is where
    > 1) not having a formal education helps. No blinders.
    > 2) not trusting what anybody has to say about anything except for
    > trusted individuals. MVPs as well as David Fenton of course,
    > fall in this category.
    ><smile>


    Well, you shouldn't trust what *I* say, either!

    I wonder, though, if this is an issue specific to Jet? Has anybody
    tested it with, say, SQL Server and MySQL and PostgreSQL, for
    example?

    --
    David W. Fenton http://www.dfenton.com/
    contact via website only http://www.dfenton.com/DFA/
     
    David-W-Fenton, Oct 1, 2010
    #11
  12. Access doesn't support enough indexes... that's one of my chief
    complaints about Access.

    I've got hundreds of tables in SQL Server that each have hundreds of
    indexes / statistics.. and Microsoft Access can't link to them.

    That's the bottom line-- going through all these extra steps in order
    to link tables-- is a pain in the ass.

    So I quit using linked tables about a decade ago.

    Keep all your data in SQL Server, and you don't have to worry about
    shit like indexing-- because indexing JUST WORKS in SQL.
    (just follow the MS reccomendations 'select * from
    sys.dm_db_missing_index_details order by statement' )

    -Aaron




    On Sep 5, 2:36 pm, "LAS" <> wrote:
    > I want to make my backend as efficient as possible, so I thought to add
    > indexes on any column that might be a parameter in a Where clause, etc.
    > When do I have to worry about adding too many indeces?
    >
    > TIA
    > LAS
     
    a a r o n . k e m p f @gmail.com [MCITP: DBA], Oct 25, 2010
    #12
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Ronda

    How many is too many

    Ronda, Apr 28, 2004, in forum: Access Table Design
    Replies:
    5
    Views:
    114
    Jesper F
    Apr 29, 2004
  2. Earl Anderson

    One-To-Many Problem is One Too Many Design Problems

    Earl Anderson, Oct 12, 2004, in forum: Access Table Design
    Replies:
    2
    Views:
    120
    Tim Ferguson
    Oct 13, 2004
  3. CAD Fiend

    Confused about one-to-many or many-to-many relationships

    CAD Fiend, Jul 6, 2005, in forum: Access Table Design
    Replies:
    4
    Views:
    339
    CAD Fiend
    Jul 7, 2005
  4. Bill R via AccessMonster.com

    Many to Many to Many

    Bill R via AccessMonster.com, Aug 24, 2005, in forum: Access Table Design
    Replies:
    7
    Views:
    111
    Tim Ferguson
    Aug 25, 2005
  5. Eric Cathell

    Many to Many to Many Normalization

    Eric Cathell, Sep 26, 2006, in forum: Access Table Design
    Replies:
    3
    Views:
    149
    Eric Cathell
    Sep 26, 2006
  6. Debra Farnham

    How many one to many relationships are too many?

    Debra Farnham, Oct 24, 2006, in forum: Access Table Design
    Replies:
    7
    Views:
    401
    Graham Mandeno
    Oct 25, 2006
  7. Nanette

    Many To Many To Many

    Nanette, Nov 27, 2006, in forum: Access Table Design
    Replies:
    4
    Views:
    120
    Nanette
    Nov 28, 2006
  8. Nanette

    Many to Many to Many Relationship DB

    Nanette, Jan 3, 2007, in forum: Access Table Design
    Replies:
    0
    Views:
    201
    Nanette
    Jan 3, 2007
Loading...