Help with Indexing

[KerryL]

I'm wondering if someone can help me understand how best to index tables. I understand that proper indexing will help speed up some of my queries and also help forms open faster, but I'm unclear on what it is to consider when I index my tables.

For instance, a couple of my forms open very slowly, specifically the order entry form (which contains a sub-form) which uses one table to pull customer info from and saves the order info to a different form. Will proper indexing (or other changes) help?

For queries, what do I need to think of when indexing a table for the most efficient querying, etc?

Is there a general rule that may help me understand what sort of indexing changes I can make to try and speed up a query or the opening of a form?

Thank you,
KerryL

 

[LLudden]

If you have many changes occuring to your database, then having too many indexes actually can slow it down (indexes have to be updated with each record modification). If you are mostly looking at existing data, then having each record that is searched on indexed can speed things considerably. Having a field indexed that is never searched on doesn't increase read performance at all, reduces write performance.

 

[allanon]

Indexing on searched fields is a deffinate plus. If you can keep most of your searching to the key fields that is a big plus (although not always practical). Try also to keep your keys as numeric as much as possible. It is faster to find record key 123 than abc (once again not always possible). Like LLudden stated above, do not index fields that you are not going to search on. It puts unneeded work on the app. Indexes will for the most part speed up searching and retrieving data but slow down updating data. Kind of a trade off there.

 

[cheerio]

The most important indexes are the primary key for each table. These are basically standard indexes with a rule that items must be unique and nulls are not allowed.

Otherwise, index if you are going to search often on a field or if you are going to have queries that select on a field.

It does not help to index fields that have a very small number of different values such as male/female as Access will find it as quick to go through the table as the index.

Generally a form and subform will be linked on key fields that are already indexed. If so other indexes will not improve the performance of the opening of the form.

Ken

 

[zambezibill]

Keep your field sizes as small as reasonbly possible on indexed text fields. Don't set the field size to 255 characters when 50 will do. Access may only allot space based on the length of the data string, but will have to search the full field width for indexing/sorting.

Cheers, Bill