[AccessD] Indexing a Memo Field.

Drew Wutka DWUTKA at marlow.com
Thu Apr 3 19:26:45 CST 2003


I think you are absolutely right.

Tell me about it.  The indexes I built for my Access .mdb memo fields take
up 130% of the space used for the actual database!  <Grin>  Though the time
to search dropped by a few thousand percent, so it outweighed the space.
Besides, who cares about drive space! <evilgrin>

Drew

-----Original Message-----
From: Seth Galitzer [mailto:sgsax at ksu.edu]
Sent: Thursday, April 03, 2003 9:44 AM
To: accessd
Subject: Re: [AccessD] Indexing a Memo Field.


Drew,

As I understand it, a memo field is different from a text field.  It is
my understanding that a memo field is really nothing more than a
pointer, kind of like an OLE field.  The text within a memo field is not
stored directly in the table, but in its own storage area that the field
points to.  So when you are indexing a memo field, all you are really
doing is indexing the pointers, not the data.  I'm guessing that the
Access dev team decided to add it as an index option more for
completeness, rather than any practical purpose.

It should be noted that "big-iron" database servers do allow you to
index text (and memo fields, whatever they may be called) fields on
their full contents.  This adds significant size to your indexes, but
makes for very fast searching on these fields.

Seth

On Thu, 2003-04-03 at 00:11, Drew Wutka wrote:
> 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

-- 
Seth Galitzer			sgsax at ksu.edu
Computing Specialist		http://puma.agron.ksu.edu/~sgsax
Dept. of Plant Pathology
Kansas State University

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


More information about the AccessD mailing list