How to know if # of indeces is too many?

[LAS]

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

 

[John W. Vinson]

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

 

[Tony Toews]

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/

 

[John W. Vinson]

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

 

[Tony Toews]

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/

 

[Douglas J. Steele]

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!)

 

[John W. Vinson]

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

 

[David-W-Fenton]

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.

 

[Allen Browne]

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.

 

[Tony Toews]

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/

 

[David-W-Fenton]

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?

 

[a a r o n . k e m p f]

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