[AccessD] Indexing a Memo Field.

Okay folks, I was chatting in email with Susan, and I brought up Indexing a
Memo field.  I don't know if everyone knows what an index really does, but
essentially it is an internal 'table' within a database, that allows for
faster searchs.  How does that work?  The best way I know of, to describe
indexing is explaining something I did once for a Palm OS project.  I had a
phone list, where I wanted to be able to search by First Name, Last Name and
Phone Number.  The Palm app was exceptionally slow when trying to find a
record within a table, so instead of letting the data control 'find' a
record, I wrote an index routine.  Essentially I duplicated the data table
three times.  One sorted by FirstName, one sorted by LastName, and one
sorted by Extension/PhoneNumber.  I create three seperate tables, which
would be the 'indexes' for the three duplicates.  Two of the 'index tables'
were pretty much the same.  (All three had the same structure).  The two
relating to first and last name had the first field as the 'letter' (which
would be the first letter in the last or first name), the second field was
the record position that was the start of that letter, and then the last
field was a 'string' that contained the position of the next letter after
the first.  The Number table was similar, but it tracked the position of the
numbers.

Thus, when a user entered 'D', the code would jump to the fourth record in
the index (since D is the fourth letter), then it would look at the second
field and find that D started at position 50.  So it would then jump to
position 50 on the main table (which was sorted by first name), and
beginning displaying the records starting with D.  If the user entered R,
after the D, then the code would again jump to the fourth record on the
index table, get the starting position for the D's, then it would look in
the last field, and determine where the R's started within the D's.  Again,
it now had the starting point for all first names starting with DR.

Now, the AccessD archives are at roughly 140 megs (somewhere close to that).
Doing a search on a subject field is pretty quick. So if you search the
subject field for *test*, it pulls up almost instantaneously.  However, if
you do the same search on the memo field (which holds the body of the
email), then it takes a while.  What I built for the new archives (which are
almost ready, I am just adding the capability to show a certain number at a
time), what I did was create a VB program that 'indexes' the memo fields.
It reads each memo field, splits it into the words that make up the message,
then it puts each word and what message that word belongs to into a seperate
table (in another database).  So what I end up with is a table for each
letter (a through z), and a table for non-letters.  Thus 'Drew' is listed in
IndexD.mdb, in tblWords, and the key for that record has records within
tblWordsToMessages, which then have the key's to each record in the main
database that has the word 'Drew' in it. Thus, when searching the archives
with this new process, searching time is cut immensely since every word is
indexed.

I mentioned 'indexing' a memo field to Susan, and she replied that she
'could' index a memo field.  That kind of shocked me, because I had spent
several hours on my 'indexing' service.  So I looked into this a bit.
Access 97 does not let you do it at all.  It doesn't give you an Index
option in the field properties, no does it list the Memo Fields when you go
to the 'indexes' window.  A2k, however, also doesn't list Indexed as a
property, but it does let you select Memo fields within the Indexes window.
I then tested the speed of searching.  Doing a search for *Drew* in the 97's
memo field (on an old copy of the Archive database), took about 10 to 11
seconds to display all of the records.  I converted that database to A2k
(and set Unicode compression...which was already set), and the same query
took about 22 seconds.  (Go A2k!!! <grin>).  I then set the Index for the
memo field in A2k.  No speed increase. Also, when I set that index, it took
about 5 seconds for Access to 'create' the index.  So I immediately knew it
wasn't indexing everything within the memo field.  I then modified my query
to search for Drew*.  In 97 that took about 4 to 5 seconds.  In A2k, it took
about the same amount of time.  With the index 'on' in A2k, it seemed to
take a second or two longer.....now that I found VERY odd.

So my question is, why in the world does A2k (and later versions) even give
you the option to 'index' a memo field.  It does seem to actually do
anything?!?!

Drew