Too many indexes

[Emma]

Hi I'm still trying to develop my database but I'm at a stand still as I'm
unable to create any more tables related to the main table. Here's what I'm
getting:

"Table must be saved before relationships can be created. Save now?" Yes
"The table could not be saved."
"The operation failed. There are too many indexes on the table 'Client
Information'. Delete some of the indexes on the table and try the operation
again."

What should I do?

 

[Allen Browne]

This is probably the most common limitation in Access, so don't feel bad
that you had struck it too.

JET can handle on 32 constraints on a table. Each index in your table uses
one of these. Each relationship uses one also (to enforce referential
integrity.)

When you hit the limit, your options are:
a) drop some indexes if you can afford to do without them, or if there are
spurious indexes;

b) manage some relationships manually, i.e. create the least important
relationships without referential integrity, and write code to ensure RI is
maintained whenever the data on the primary side of the relationship is
edited or deleted.

 

[Arvin Meyer MVP]

You are allowed 32 indexes on a table. Some are used hidden when you create
relationships. Often Access creates an index on any field ending in ID,
code, or num. So it is possible to have duplicate indexes on a field. Delete
the unnecessary ones, compact the database, and you should be OK.

 

[David W. Fenton]

JET can handle on 32 constraints on a table. Each index in your
table uses one of these. Each relationship uses one also (to
enforce referential integrity.)

Since creating a relationship creates a hidden index, is it safe to
not create one explicitly? Will Jet use the hidden index? If it
doesn't, what's the purpose of that hidden index?

Another thing to consider is that if you replicate your back end,
that adds indexes on two of the three base replication fields
(s_GUID and s_Generation).

A recommendation that many have made is to never index Boolean
fields (because the cardinality is so low, i.e., 2), but I've found
that not having an index really does slow things down significantly.
It defies common sense, but there it is (Tony Toews has confirmed
it, if I'm remembering correctly). The other advice is to not index
sparsely populated indexes, i.e., where large numbers of the records
are Null.

I think that for single-table filtering/sorting, it's often
counterproductive to remove those indexes. On the other hand, if the
filter/sort on the field that is a candidate for losing its index is
one that is generally only used in a join with another table in
combination with criteria on the other table, it can cause very
little performance difference, as the size of the dataset that has
to be scanned without benefit of an index is reduced by the criteria
on the other table and the index join between the two tables. But
you'd have to test with real-world scenarios to see if it's enough
to make a difference.

A fairly drastic solution would be a 1:1 partition of the table,
which would then double the number of indexes available. But that
causes a whole host of other problems.

 

[John W. Vinson]

Since creating a relationship creates a hidden index, is it safe to
not create one explicitly? Will Jet use the hidden index?

In my experience, yes and yes.

 

[Paul Shapiro]

Check the Access option for automatically creating indexes. That can create
duplicate indexes, or indexes you don't need. By default any field that
contains ID, code, etc. (I don't remember the exact settings, but you can
see it in the options) will be indexed. I always delete that setting and
create indexes I choose. Access also creates hidden indexes for all foreign
key fields that have relationships enforced, and there's no option to
disable that feature, so don't create duplicates of those.

 

[John W. Vinson]

Hi I'm still trying to develop my database but I'm at a stand still as I'm
unable to create any more tables related to the main table. Here's what I'm
getting:

"Table must be saved before relationships can be created. Save now?" Yes
"The table could not be saved."
"The operation failed. There are too many indexes on the table 'Client
Information'. Delete some of the indexes on the table and try the operation
again."

What should I do?

One thing that might help is some code to list all of the indexes anywhere in
the database. As noted elsethread there may be some duplicates or unneeded
indexes. Try this code:

Sub ShowAllIndices()
Dim db As DAO.Database
Dim tdf As TableDef
Dim idx As Index
Dim fld As Field
Set db = CurrentDb()
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
Debug.Print tdf.Name
For Each idx In tdf.Indexes
Debug.Print Tab(5), idx.Name
For Each fld In idx.Fields
Debug.Print Tab(10), fld.Name
Next fld
Next idx
End If
Next tdf
End Sub

 

[Tony Toews [MVP]]

Hi I'm still trying to develop my database but I'm at a stand still as I'm
unable to create any more tables related to the main table. Here's what I'm
getting:

I have a tool available that shows you the duplicate indexes that
others have mentioned in this thread. Please email me for the tool.
Note that this tool can be a bit dangerous when it comes to
performance and such so you have to be careful with it.

Tony

 

[David W. Fenton]

In my experience, yes and yes.

I've been kind of lackadaisical with this. I do recall about 10
years ago taking a replicated database and carefully pruning all the
duplicate indexes -- I had ones I'd added manually for all foreign
keys, ones I'd added manually for primary keys, ones that were
created automatically for fields ending in ID, and the hidden
relationship indexes, and the result when I got rid of it all was a
reduction in size of a 20MB replicated back end down to about 16MBs.
I was impressed.

But for some reason, I didn't incorporate the principles learned
there into my regular work, possibly because everything I've been
doing has just been working. I should probably be more careful with
this kind of thing.

 

[Tony Toews [MVP]]

I've been kind of lackadaisical with this. I do recall about 10
years ago taking a replicated database and carefully pruning all the
duplicate indexes -- I had ones I'd added manually for all foreign
keys, ones I'd added manually for primary keys, ones that were
created automatically for fields ending in ID, and the hidden
relationship indexes, and the result when I got rid of it all was a
reduction in size of a 20MB replicated back end down to about 16MBs.
I was impressed.

I'm surprised by the reduction in size. If an index is identical
Access/Jet does not create a new index.

1) I tested this myself in Access 2000
2) There was a bug in an older msjet40.dll or msjet35.dll where if
there was already an index with an ascending field and a duplicate
index but with a descending field was created then Access/Jet didn't
create a new index like it should've.

But for some reason, I didn't incorporate the principles learned
there into my regular work, possibly because everything I've been
doing has just been working. I should probably be more careful with
this kind of thing.

Dunno about you but I only get careful about this when I get the OP
message. then I poke about.

Tony

 

[David W. Fenton]

I'm surprised by the reduction in size. If an index is identical
Access/Jet does not create a new index.

1) I tested this myself in Access 2000

This was Access 97, so maybe Jet 3.5 wasn't that smart.

[]

Dunno about you but I only get careful about this when I get the
OP message. then I poke about.

To be honest, I seldom end up building databases from scratch any
more. I'm mostly fixing existing databases, and I guess that's why I
never quite get around to doing things right.