terpsichore
asked on
composite indexes in Access
Dear experts -
I am now fine-tuning an application that is basically working well.
We have indexes defined in all tables on key fields.
However, we have not defined ANY composite indexes (if I'm using the term correctly - I mean an index based on more than one field).
The real workhorse of the system is a Project table, that has a number of child tables, most notably one called Project_Parts. Do we, for example, want to have a project_ID + Part_ID composite index on the parts table? Would this be standard, or not?
I used to work a lot in Foxpro, and indexes like this made a HUGE difference.
a related question - nowhere in our system do i 'tell' the code to use a given index; a programmer told me once that Access takes care of all this and 'knows' which indexes to use.
Can someone give me a bit of orientation on the above?
Thanks!
I am now fine-tuning an application that is basically working well.
We have indexes defined in all tables on key fields.
However, we have not defined ANY composite indexes (if I'm using the term correctly - I mean an index based on more than one field).
The real workhorse of the system is a Project table, that has a number of child tables, most notably one called Project_Parts. Do we, for example, want to have a project_ID + Part_ID composite index on the parts table? Would this be standard, or not?
I used to work a lot in Foxpro, and indexes like this made a HUGE difference.
a related question - nowhere in our system do i 'tell' the code to use a given index; a programmer told me once that Access takes care of all this and 'knows' which indexes to use.
Can someone give me a bit of orientation on the above?
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"that some people use a combination of fields as the primary key"
I have yet to see a valid reason for creating a compound primary key - as opposed to using the AutoNumber.
Just for starters, a compound primary key immediately complicates creating relationships between tables, since now - say for the foreign key in that Many table of a One to Many relationship, you need all the fields (2.3.4, etc) of the primary key - instead of just one field - Long Integer - to match the Auto Number.
And another not so well known tid bit. When you enable Referential Integrity - again say between two tables in a One To Many relationship, Access automatically creates a 'hidden' Index on the foreign key - which you can see if you iterate the Indexes collection.
mx
I have yet to see a valid reason for creating a compound primary key - as opposed to using the AutoNumber.
Just for starters, a compound primary key immediately complicates creating relationships between tables, since now - say for the foreign key in that Many table of a One to Many relationship, you need all the fields (2.3.4, etc) of the primary key - instead of just one field - Long Integer - to match the Auto Number.
And another not so well known tid bit. When you enable Referential Integrity - again say between two tables in a One To Many relationship, Access automatically creates a 'hidden' Index on the foreign key - which you can see if you iterate the Indexes collection.
mx
ASKER
just what i was looking for - will start delving in to this. many thanks to all.
You are welcome.
Remember: testing testing testing ...!
Remember: testing testing testing ...!
Couple of comments:
<<I used to work a lot in Foxpro, and indexes like this made a HUGE difference. >>
There are two issues here; design and performance.
Design has been touched on above. In regards to peformance, JET will use a composite index, but only of the order of the index exactly follows the search given, so in general, you are better off with individual indexes and let JET do index merge-joins with Rushmore.
JET doesn't have all of the Rushmore optimizations that FP had, but it has some of them.
You also don't want to index anything with low cardinaility. That is fields with few possible values (like a yes/no field, which has only two possible values). JET indexes are ISAM based and ISAM doesn't do well with those. It's faster to let it do a table scan.
Also in regards to the design issues, you might want to read through:
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2041-The-great-PK-debate-Natural-Keys-vs-Surrogates-again.html
Which goes into key usage/design and even why you still might need a composite index (to form a constraint) even though your using a AN for a "PK".
<<a related question - nowhere in our system do i 'tell' the code to use a given index; a programmer told me once that Access takes care of all this and 'knows' which indexes to use.>>
That is correct. There is no HINTS or anything similar that can be used to tell JET which indexes to use. It decides on it's own the best cost with the given indexes.
Jim.
<<I used to work a lot in Foxpro, and indexes like this made a HUGE difference. >>
There are two issues here; design and performance.
Design has been touched on above. In regards to peformance, JET will use a composite index, but only of the order of the index exactly follows the search given, so in general, you are better off with individual indexes and let JET do index merge-joins with Rushmore.
JET doesn't have all of the Rushmore optimizations that FP had, but it has some of them.
You also don't want to index anything with low cardinaility. That is fields with few possible values (like a yes/no field, which has only two possible values). JET indexes are ISAM based and ISAM doesn't do well with those. It's faster to let it do a table scan.
Also in regards to the design issues, you might want to read through:
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2041-The-great-PK-debate-Natural-Keys-vs-Surrogates-again.html
Which goes into key usage/design and even why you still might need a composite index (to form a constraint) even though your using a AN for a "PK".
<<a related question - nowhere in our system do i 'tell' the code to use a given index; a programmer told me once that Access takes care of all this and 'knows' which indexes to use.>>
That is correct. There is no HINTS or anything similar that can be used to tell JET which indexes to use. It decides on it's own the best cost with the given indexes.
Jim.
ASKER
wow - thanks so much. truly insightful.
ASKER
Any guidance here, or articles that give a good overview of optimizing indexing in Access? Thanks -