[AccessD] Indexing a Memo Field.

Drew Wutka DWUTKA at marlow.com
Thu Apr 3 16:58:31 CST 2003


I'd have to agree too.  It's odd that 97 prevents it, but 2k doesn't.  Wait,
never mind, that's not odd, we're talking about A2k! <grin>

-----Original Message-----
From: Mitsules, Mark [mailto:mitsules_ms at nns.com]
Sent: Thursday, April 03, 2003 10:57 AM
To: 'accessd at databaseadvisors.com'
Subject: RE: [AccessD] Indexing a Memo Field.


According to the help file:
"Note   You can't index Memo, Hyperlink, or OLE Object data type fields."

Therefore, I would tend to agree with Seth that the option to set an index
was "...more for completeness, rather than any practical purpose.".  ...or,
they just forgot to disable the index control when dealing with memo fields.


Mark

-----Original Message-----
From: Seth Galitzer [mailto:sgsax at ksu.edu] 
Sent: Thursday, April 03, 2003 10: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
_______________________________________________
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