Drew Wutka
DWUTKA at marlow.com
Tue Jun 10 10:48:51 CDT 2003
I am speaking just from experience and logic, so my details may not be perfect, but from what I have seen, this should hold true. An index is really based on sorting. Let me explain with an example I have used a few times before. I wrote a Palm application that displayed a company phone list. The VB version on our network allows for almost instantaneous searchs of first name, last name, or phone number....(all the same search box). When I took the same table to the Palm, the searching slowed to a crawl. I was using a Palm Vx, which is something like 12 mhz. That's SLOW. To speed things up, I built my own indexes...literally. I knew I was going to search by first name, last name and extension. So I built three identical tables, each one sorted one of the three ways. I then built an index table, for each of the three tables. The index table had 3 fields. The first field was the starting character (A through Z for first name and last name sorts, and 0 through 9 for phone number). The second field then recorded the starting position of that letter, and the last field showed the starting position, relative to the first starting position, for the second character (4 bytes for each character...ie, 000100020005....would show for the first or last name indexes that XA started at one record after where X starts, XB 2 records, XC 5 records. (since XA is +1, that means there would be an X record...) Thus, if someone searched for 'Bart', I would go to the first name index, and jump to the second record (B), which would tell me that B starts at 51, the first four characters of the last field in the index are 0000, so I know that BA starts at 51. I then check the next character (BB) which shows 0010, which tells me I have 10 records that are BA, so I know now where to start looking. This immensely sped up my search. Now, the key is, the index is showing positions to find the records, so the more ways you want to find your data, the more complex your indexes are going to be. If a relationship has an index, but the fields don't, then searching the individual fields will not be sped up with an index. Make sense? I could just be babbling, haven't had a good dose of caffiene yet! <grin> Drew -----Original Message----- From: Mark Whittinghill [mailto:mwhittinghill at symphonyinfo.com] Sent: Tuesday, June 10, 2003 11:29 AM To: accessd at databaseadvisors.com Subject: [AccessD] Indexes in Access Hi all, I know how Access creates an index automatically when a field is used in a relationship. For example, if a table is joined on PersonID, there is an index on PersonID, even though I won't see it in the list of table indices in design view. I would think this index would also be used to speed up searches, like an index I would add myself, in which case I wouldn't want to add an Index for PersonID, as that field would have duplicate indices. Correct? Mark Whittinghill Symphony Information Services 612-333-1311 mwhittinghill at symphonyinfo.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com