Fun fact: Access can create too many indexes

[spikepl]

Fun fact: Access can create unwanted multiple indexes

I have to define some more indexes, and in some of the tables, I already had an index on the Primary Key, but also a separate index on the same field! This doesn't fail the system as such, but does not improve performance on insert/delete either.

Here is an explanation: http://www.applecore99.com/tbl/tbl004.asp

Just in case that page should go away, here is the full story:

By default, Access indexes certain fields for you automatically, based on the name that you call the field. By default, this happens if the field has 'ID', 'code' or 'num' in the field name, then Access creates an index. This can cause a duplication of indexes if the field is also the Primary Key for the table, having one called PrimaryKey, and the other called the name of the field. Although it is a very good idea to have indexes on fields in the table, as this speeds the retrieval of data, having too many indexes will slow the deletion and creation of records.

To prevent Access from creating these indexes automatically, from the database window, select Tools|Options, and then the Tables/Queries tab. Remove the text from the field labelled 'AutoIndex on Import/Create'. Note that this is an application wide setting, so once you have set this once in an Access database, you won't need to change it again.

To remove any existing duplicate indexes, you will need to open each table in design mode, and then select Indexes (View|Indexes). If there is a duplicate index on a field, then remove it by right-clicking on the duplicate row, and selecting 'Delete Row'.

Conclusion? Check you indexes.

For A2007: Access Options -> Object Designers->Query Design->AutoIndex on Import/Create

 

[gemma-the-husky]

i wondered why some fields seemd to get automatic indexes.

out of interest did you know that access also builds a hidden index for the FK side of any relation.

 

[spikepl]

out of interest did you know that access also builds a hidden index for the FK side of any relation.

Hidden index? So that it is not visible in the indexes of the table containing the FK? This sounds strange.

Otherwise I guess this is just the same mechanism, based on the name of the field (more likely than not containing "ID") that is in play here. I just built two related tables with an FK not containing "ID" or "num" or "code" and no visible index was automatically created for the FK.

 

[gemma-the-husky]

i don't think it is so strange - the index is there to manage the relation, and therefore i don't think access (MS) wan't people messing around with the index in any way.

the index has an attribute of foreign set to "true" - and can be inspected if you step through the indexes in code.

note that if you already have too many indexes on the table, and access cannot add this index, it reports that it cannot construct the relation either!

 

[spikepl]

That is indeed very interesting. And no, I did not know that. But makes sense, in the overall context of Access as a tool for anybody.

To state the obvious: it means that one does not have to specifically create indexes on foreign keys, neither by the automated mechanism using ID/num/code nor manually.

 

[gemma-the-husky]

yes, but only if you create the relation. you can still use the PK/FK structure in queries without specifically setting up the relationship