danlopez007
asked on
how do ms access indexes work
Just what to how what method does Access use to retrieve records. That is, read entire index first then retrieve related records. And how
Thanks
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What is the reason for your question?
Are you searching for a way to speed up your queries?
Typically, we don't worry too much about how the index works. We just know the "good rules" to apply (don't index on everything, use numbers for primary keys and such, avoid index of long text fields, etc.)
Scott C
Are you searching for a way to speed up your queries?
Typically, we don't worry too much about how the index works. We just know the "good rules" to apply (don't index on everything, use numbers for primary keys and such, avoid index of long text fields, etc.)
Scott C
Note that indexes can be a double edge sword. Whereas they will (usually) most definitely speed up searches and filtering - almost like magic sometimes, each time you add or delete a record, all indexes in the table have to be updated by Jet. So, if you have several hundred thousand records, then the updating time could be significant, depending on various aspects of your system and/or network connection is there is one, etc.
Indexing can be somewhat of an art form. The bottom line is be judicious in your use of indexes. Don't just randomly index fields for no reason. In addition, test the effectiveness of your indexes on test (or live) datasets that represent the amount of data you expect to encounter ... and see for yourself how they affect performance, good or bad.
mx