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.

A further refinement of the question - I see, reading a bit online, that some people use a combination of fields as the primary key. Right now, I have the primary key in each table as simply the unique record ID.
Any guidance here, or articles that give a good overview of optimizing indexing in Access? Thanks -

"Right now, I have the primary key in each table as simply the unique record ID."
Autonumber I assume. Stay with that.

"Do we, for example, want to have a project_ID + Part_ID composite index on the parts table? "
Indexes are most useful for criteria, sorting - and grouping (in a query or report).
If ... you have for example a query that has filtering criteria (or sorting) on the fields Project_ID and Part_ID - in that order left to right, then a composite index *may* help. The *only* way you are going to know for sure is ... testing, specifically on a representative size recordset of data.

Tweaking indexes is more of an art form than not.

More (my standard post on indexes):

Here are some sources with a lot of good information. I doubt if there is any once 'best' source, as there is much conflicting information on the subject. Doing one's own R&D on the subject (read: testing) is probably the most prudent approach.

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.

"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.

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

<<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.