If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
To index or not to index
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 -- This e-mail address is fake, to keep spammers and their address harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the newsgroup when possible, so that all may benefit from the exchange of ideas. |
#2
|
|||
|
|||
To index or not to index
Yes: it would make sense to index the date field if you need to use it like
that, particularly with 80k records. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Petr Danes" wrote in message ... 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. |
#3
|
|||
|
|||
To index or not to index
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 "Allen Browne" píše v diskusním príspevku ... Yes: it would make sense to index the date field if you need to use it like that, particularly with 80k records. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Petr Danes" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|