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.