To index or not to index

[Petr Danes]

I have a table of stuff stored in a repository and an attached table of
inventory dates, linked one-to-many by an Autonumber ID field. I regularly
need to find the oldest or newest inventory dates (or all, in order by date)
for each item record in the inventory table, which is normally an automatic
case for indexing. But this stuff is not inventoried very often, so far,
only two out of over 80,000 records have three records in the inventory
table, all others have zero, one or two inventory records. This is NOT going
to change. It will likely be decades before there are as many as ten
inventory records for any item record, and then it will not be for very
many. I doubt if this database will live to see the day, although I'm trying
to make it as useful and robust as I can.

Given such a small number of detail (inventory) records per item record,
does it make any sense to create an index on the date field? I only need to
look up records in conjunction with the master item record, never by date
alone.

Pete

 

[Allen Browne]

Yes: it would make sense to index the date field if you need to use it like
that, particularly with 80k records.

 

[Petr Danes]

All right, Allen, I'll try it. I thought that since I'm retrieving only only
one or two inventory records for each item record, it might not make sense
to further index the date field, that examining an index might actually be
slower than simply looking at one or two date fields directly.

But if you say so, I'll give it a shot.

Thanks,

Pete