two indexes on one field? (1 Viewer)

wazz · Jul 27, 2008

i've noticed that two indexes are created on the same field.

Code:

index name   |   field name
---------------------------
primary key  |   UserID      (for example)
UserID       |   UserID

Why is that (UserID as a foreign key maybe?) and can one be deleted?

Banana · Jul 27, 2008

I'm sure you can delete one index without any repercussions. The real question, however, is how did that get created in first place?

Normally, Access automatically create an index for any primary key (and possibly for any relationship defined, but will need to recheck that) but I don't believe it creates index without user prompting for it.

Is this for a linked table where you created a index manually at time of linking?

The_Doc_Man · Jul 27, 2008

It is not uncommon to have multiple non-primary keys on a single field if they are multi-field. It is rare to have multiple single-field keys on the same field, since all but one of them are redundant.

IF that second key is a single-field key and no external program or VBA snippet names the key, you can delete it. The latter condition is obviously the harder of the two to ascertain. But since it is your DB, I would guess you could answer the latter half of those conditions off the top of your head.

wazz · Jul 27, 2008

it's not a linked table or a multifield pk. i just started creating this db from scratch and i haven't even set any relationships yet. i'm just staring at the relationships window and tweaking the tables.

this multiple-index thing has always happened, afaik.

however, i'm thinking now, i have changed a few field names and a number of field properties all over the place. i have noticed that old field names linger in the index even after names are changed, but, i still see multiple indexes on a few names that haven't been changed.

odd that the old names stay in the table, indexed. hmmm... maybe it's because i changed the name in one place and the old name stays until i fix the names in the other tables...but, they still aren't removed after changing all the names in all the tables. i'll try to be really vigilant and see if the name-changing is causing this or if it's automatic for some reason.

(It is not uncommon to have multiple non-primary keys on a single field if they are multi-field. --> that kind of hurts my brain a bit).

Banana · Jul 27, 2008

Wait a minute.

1) Did you have AutoCorrect off? It has several problems as Allen has listed, but doesn't explicitly list indexes, though I do wonder about the table/queries names...

2) Did you repair & compact? It should delete the old indexes.

As for Doc's point, he's telling you it's common to have multiple indexes looking like this:

Code:

Primary   ID
FullNameIndex  LastName, FirstName
LastNameSSN   SSN, LastName

LastName appears twice, but in a different set of index for different kind of search so in this context, it's A-OK. But to have two same kind of indexes for only one field?

One more point. Would you by any chance have created indexes, either in code or by altering the index property in table design view?

wazz · Jul 27, 2008

Banana said:
1) Did you have AutoCorrect off? It has several problems as Allen has listed, but doesn't explicitly list indexes, though I do wonder about the table/queries names...

no, it's on. i kind of like it and - generally - isn't a big deal for the (usually) small things i do. but i'll fool with it to see how it impacts indexes.

Banana said:
2) Did you repair & compact? It should delete the old indexes.

no i hadn't yet. i was just starting the db and hadn't set everything. (it's only been open twice actually). most of my db's are set to do this on close (though i know some people are dead set against it) and i'll also look at how this affects things.

Banana said:
As for Doc's point, he's telling you it's common to have multiple indexes looking like this:

Code:

Primary ID FullNameIndex LastName, FirstName LastNameSSN SSN, LastName

LastName appears twice, but in a different set of index for different kind of search so in this context, it's A-OK. But to have two same kind of indexes for only one field?

i see

Banana said:
One more point. Would you by any chance have created indexes, either in code or by altering the index property in table design view?

didn't create any in code but have done in design. that's when i noticed all the extra indexes. (i've seen this many times before, just never checked on what was going on). i was adding an index to a composite (junction) table to avoid duplicates because i added a new pk.

Banana · Jul 27, 2008

All right, sounds like a compact & repair should fix that duplicate index problem then.

I usually don't worry about this while I'm elbow deep in design process and there's so many garbage (indeed, I once got my nightly .mdb to 30 MB!!! After a C & R, it was just 2 MB.)

wazz · Jul 29, 2008

all righty. seems the following happens:
- new table, add the first field (index window is open so i can see what's happening as i add the field) -> field is added to the indexes as soon as i leave the field name "textbox".
- change the field to be a PK -> the field is added a second time to indexes, this time as PK.
- this happens every time, with autocorrect on or off, and the "extra" (first)(non-pk) index is not deleted on compact.

- the other thing is that if i change a field name, then in the indexes the field name is updated (to match the change) but the index name is not. this might be what was throwing me off in the first place.

so i suppose i will:
- manually delete the extra single indexes that are left over after making a field a PK;
- change the index names to match the field name where necessary (but i can't imagine that internally it matters);
- do what banana suggests and wait until things are more finalized (more finalized?).

Pat Hartman · Jul 29, 2008

Under Tools/Options on the Tables/Queries tab is a property called "Auto Index on Import/Create". Remove any values from this field. This is telling Access to automatically create indexes based on the suffix of a field name. This is one of those help those who can't help themselves "features". Access creates these indexes because it assumes that you don't know enough to create your own indexes. Jet automatically creates indexes on foreign keys but hides them so any index you/Access also create for a foreign key will be a duplicate. Once you have changed the setting, go through each table and remove any of the extraneous ones. You should have a primary key for each table and you should index any non-key field that you want to search on but don't index fields that have only small subsets of values such as Female/Male because Jet will never use them. Keep in mind that while an index may be useful in speeding up a search, it will also slow down the updating process and take up room in the database.

DCrake · Jul 30, 2008

Simple Software Solutions

Just to interject I have noticed in the past that if you are browsing a table in datasheet mode and do an A-Z sort on a particular field then close the datasheet Access will ask you if you want to save the changes. If you say yes it can have a tendancy to create an index based on the last sort performed.

If this sort was done on an existing indexed field then there may be a possiblity that a duplicate index is created.

CodeMaster:

Pat Hartman · Jul 30, 2008

Which version of Access does that happen in? I haven't noticed it. Your sort sequence is saved but I've never seen an index created based on that.

wazz · Jul 31, 2008

thanks pat. removing values from "Auto Index on Import/Create" stops the behaviour. still (a bit) odd that the field was duplicated when adding the pk. thanks also for the excellent tip on fk's. i had no idea that the visible fks are actually duplicates (of hidden fks). i will remove all of those, too. ciao.

Pat Hartman · Aug 1, 2008

That one was a surprise to me also.

two indexes on one field? (1 Viewer)

wazz

Super Moderator

Banana

split with a cherry atop.

The_Doc_Man

Immoderate Moderator

wazz

Super Moderator

Banana

split with a cherry atop.

wazz

Super Moderator

Banana

split with a cherry atop.

wazz

Super Moderator

Pat Hartman

Super Moderator

DCrake

Remembered

Pat Hartman

Super Moderator

wazz

Super Moderator

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread