A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

To index or not to index



 
 
Thread Tools Display Modes
  #1  
Old May 11th, 2010, 04:29 PM posted to microsoft.public.access.tablesdbdesign
Petr Danes[_5_]
external usenet poster
 
Posts: 31
Default 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.


Ads
  #2  
Old May 12th, 2010, 04:22 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default 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  
Old May 12th, 2010, 11:43 AM posted to microsoft.public.access.tablesdbdesign
Petr Danes[_5_]
external usenet poster
 
Posts: 31
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:50 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright ©2004-2019 OfficeFrustration.
The comments are property of their posters.