Results 1 to 8 of 8

Thread: Index Amounts

  1. #1
    Join Date
    Nov 2002
    Posts
    154

    Question Unanswered: Index Amounts

    Supposedly the limit is 32 indexes per table in Access. My understanding, with respect to other databases, is that indexes are stored as part of the table that they reside in. But, I have a table that gives an error about too many indexes in which only if Access counts the Foreign Keys that the Primary Key of this table are involved in also could the index count for that table possibly be 32. I am just curious, does anyone know if Access works this way? Does an index get put in the parent table for each Foreign Key? I thank you in advance for your time and your responses.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    32 indexes on 1 table - wow. Your primary key is automatically indexed and probably does count as one of the 32 for that table. An index on another table doesn't count as an index for a different table. Why so many indexes? I usually have 6-12 indexes on 1 table. Are some of them clustered indexes?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    With regards to clustered index
    Jet has one clustered index per table, that is the primary key index.

    Access will also automatically create indices for fields ending with some letters (ID, CD, Num etc), I don't remember them all, but they can be found - and turned off - somewhere in Tools | Options - probably under the Tables/Queries option (AutoIndex on import/create?). Indices created this way, can easily be turned off through the interface.

    But the interesting thing was the foreign key - and yes, Jet automatically creates index on foreign key fields when you create a relationship, if the field(s) aren't already indexed. I don't think you can remove these through the interface, but if you create the index through DDL/ADO OLEDB, then you could create a fast foreign key. Look up the topic Foreign keys here http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx for more info.

    So yes, creating foreign keys usually adds an index, and even though it isn't usually visible or accessible through the interface, it adds to the index count. One way of avoiding it is described above, and the link explains some situations where such method can be appropriate.
    Roy-Vidar

  4. #4
    Join Date
    Nov 2002
    Posts
    154

    Unhappy Thanks but...

    Thank you very much for your response Roy-Vidar. But, physically looking through the table and running this function that I made (see attached) only yields 11 indexes on this table. Most of these indexes are lookup type stuff. There is something strange going on here but I can't figure it out--something is inflating the amount of indexes that Access sees for this table and the only thing that I could figure out is that this might be true if Access "charged" this parent table for indexes in other tables (foreign keys of its primary key in other tables). I wonder if anyone else has noticed strange behavior like this? Also, I pull all my backend tables into another fresh database regularly as maintenance and when I do I put them into the new db without indexes and use a subroutine with many ALTER TABLE statements to add in all Primary Keys, Foreign Keys and indexes. I have been using DDL and the command object for years, actually. But, I thank you for that page and the bookmark in my browser that it yielded . I thank you all in advance for your time and your responses.
    Attached Files Attached Files

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by RoyVidar
    Access will also automatically create indices for fields ending with some letters (ID, CD, Num etc), I don't remember them all, but they can be found - and turned off - somewhere in Tools | Options - probably under the Tables/Queries option (AutoIndex on import/create?). Indices created this way, can easily be turned off through the interface.
    Really? That is, frankly, absurd. Thanks for the gotcha
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Worst case scenario, is table corruption (see for instance http://www.granite.ab.ca/access/corr...nyindexes.htm), but given you've both dropped and recreated indices and recreated the whole stuff in a new db, that's probably not likely.

    I've read somewhere that one table cannot be the referenced table for more than 35 referencing tables. Twas an Access MVP stating this (Pat Hartman), but I have no clue whatsoever why, or how to determine such, but should it be true, then it might indicate there might be some kind of overhead in the referenced table, too, as you suggest.

    Perhaps you'll find the "second branch" of this discussion from cdma interesting (TC's explanation) http://groups.google.com/group/comp....528ae3c290275/.

    We're now far out of my league, and I'm sorry, but I can't offer any more. Hopefully, someone else can chime in.

    pootle flump
    thanx, it's just one of the defaults that can be turned off I usually turn stuff like that off when I start working with a new version
    Roy-Vidar

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Good grief - now I see it I know I have "read" it a million times before.

    Quote Originally Posted by Access Options
    AutoIndex on Import\ Create
    ----------------------------------
    ID;key;code;num
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2002
    Posts
    154

    Wink Meh

    Well, it looks like relationships do count in that 32, and that would certainly explain it. Thanks Roy.

Posting Permissions

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