Solved Indexes? (1 Viewer)

[Gasman]

Hi all,
I have constructed a small DB to keep track of a college reunion I am organising.
I organised one in 1996 and produced a newsletter for what had gone on from 1971 when we started, to the reunion.

So I decided to add a memo field for 2021 as I had one for 1996 (all that was needed at the time). Then decided I would have a table to hold the news notes and link to the Cadet table.

The tblHistory has fields
HistoryID
HistoryCadet - FK from Cadet table
HistoryYear - 1996 or 2021
HistoryNotes - Memo field

I have then changed the History fields in the cadet table to Dlookup the data from the tblHistory table.

That is the background.

My question is I know I should have indexes on HistoryCadet and HistoryYear, but can I make an index of both fields, as they will be unique, or do I have to have two individual indexes, one for each field.?

I only have 90 records in the cadet table, so a potential of 180 in the History table, so cannot see any speed difference, but curious to know. the best way?

TIA

 

[The_Doc_Man]

For less than a few hundred records, indexes are ALMOST invisible, time-wise. But from a theoretical viewpoint, you put indexes on the fields that you will search. Putting compound indexes would be right if you planned to search by compound indexes. Indexes would also be right if you have a relationship between two tables. The one-side of a one-many setup should be indexed.

Uniqueness in an indexed field is not a requirement, but if you know that a particular pair of fields has a compound key uniqueness constraint, that is another reason to add an index. Usually, you put indexes for searches, relationships, or constraints. If none of those apply, you probably don't need the index.

 

[jdraw]

Article on Composite unique index.
Good luck with your project.

 

[Gasman]

@The_Doc_Man I am not searching on the compound index, just that both of those fields are in the DLookup

=NZ(dlookup("HistoryNote","tblHistory","HistoryCadet = [CadetID] AND HistoryYear = 1996"),"")

so from your reply, I may as well just stick with an index for each field, unless I need the compound index to force uniqueness when the fields are combined.?

@jdraw
Thanks for the link, I'll check it out tomorrow.

 

[The_Doc_Man]

I may as well just stick with an index for each field, unless I need the compound index to force uniqueness when the fields are combined.?

Yes. If the index serves no purpose, you don't need it and therefore don't want the overhead that it represents.