To index or not to index
in [Tables]
|
From: Petr Danes on 11 May 2010 11:29 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.
From: Allen Browne on 11 May 2010 23:22 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" <skruspammers(a)no.spam> wrote in message news:euWRE7R8KHA.3276(a)TK2MSFTNGP02.phx.gbl... > 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.
From: Petr Danes on 12 May 2010 06:43 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" <AllenBrowne(a)SeeSig.Invalid> p�e v diskusn�m pr�spevku news:eetFQJY8KHA.420(a)TK2MSFTNGP02.phx.gbl... > 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" <skruspammers(a)no.spam> wrote in message > news:euWRE7R8KHA.3276(a)TK2MSFTNGP02.phx.gbl... >> 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. >
|
Pages: 1 Prev: Search Form runtime error 2448 Next: how do i activate the overtype function in access |