Sorting out indexes mess

[PeregrinTook]

Hey all,

I just found this article (http://www.applecore99.com/tbl/tbl004.asp) about indexes that talks about Access automatically creating two indexes on the primary key of a new table. I looped through the back end db of my app to return a list of indexes and associated fields for each table, and sure enough was surprised (perhaps wrongly??) at the results.

It looks like on any tables you create manually (ie thru clicking New Table Wizard), you do get two indexes on the table's primary key: one called PrimaryKey and one called the same as the actual field name. But on any tables you create through code (well using the SQL CREATE statement anyway) you get a single index called something like 'Index_CA67B281_435D_4772' on the primary key.

And because I've also added some indexes of my own through code (before I realised Access tried to help me), I now have quite a mess of indexes and reckon I should clean it all up a bit. So I'm thinking of just modifying the loop I made earlier to clear all existing indexes, then add new ones that I define from scratch - this should hopefully solve the problem. But in taking the time to do this, I want to make sure I get it right now. Therefore, what's the thoughts around indexes on tables:

I'm fairly sure any primary & foreign keys should be indexed as these are the primary identifiers when retrieving recordsets, but what about other fields though?
Would it be fair to say any fields included in a SQL WHERE clause should be indexed for example, to improve search speed?
Are there any other stand-outs like this that should always be indexed in a table?
Also, an index doesn't have to be called PrimaryKey on the actual primary key of a table, does it?
Is the name of an index relevant at all?

I would like to hear all thoughts as always...

Thanks
J

 

[Banana]

To answer your questions:

1) Yes, you definitely need a index on any keys, both primary and foreign. Else, you can't update recordsets properly.

2) Indexing on other fields, especially those used in WHERE clauses may or may not improve performance (or even hurt it). You need to consider what kind of data, and how index will help.

For example, if we had a table with a checkbox for gender, it wouldn't be very useful to index that field when records are roughly even, as Jet will then decide it's just faster to just scan the entire table to return the results. BUT, if it happened that 90% of gender are males, you would see better performance if you indexed that field so you could query for who are females.

There's supposed to be a SHOWPLAN functionality with Jet but that involves mucking with registry... maybe someone can explain more...

 

[PeregrinTook]

Thanks Banana.

Hmmm, there's a lot more to indexes than first meets the eye. I didn't realise they were actually essential to update recordsets, I always thought they were just an extra that was beneficial in some way to the performance of queries etc... Having read WindSailor's post now though, I am considerably more informed - though perhaps not 100% clear yet about the best way forward for my app!

I think I want to get rid of the dup indexes for sure, and make sure every PK/FK has one index only to begin with. Then I guess I need to play around a bit and test performance using different combinations on other fields - because it doesn't sound like there's any hard & fast rules for using indexes to maximise performance, and in fact if you're not careful, the opposite could be true and you end up decreasing efficiency!

The majority of select queries I use might be something along the lines of: Get me all records where the PrimaryKey equals a given ID, a couple of flags equal zero or one, and ModifiedDate is between two given dates.

I would've thought that indexing the PK, the flags and the date field should give best performance here - but it seems that because the flags are only ever zero or one, indexing those fields will not help in the slightest. Perhaps indexing the date field in addition to the PK will be beneficial, and then again perhaps not...

Only one way to find out I guess - benchmark current performance then try a bunch of scenarios to see what works best.

Cheers
J