[AccessD] Indexes on text fields

John,

<<Nope, I cannot give you and sources and will not spend my time looking.
Go
look in any book on Access queries, sorting and indexing and I'm sure you'll
find your own.>>

  That's why I'm asking.  I have pretty much read everything there is to
read about JET over the past 10 years, and I've never seen anything on this.

<<[Fielda] & Random() & [Fieldb] & ascii(right$("John Colby",1))

How in the world is any index on FieldA and FieldB going to help you sort
that expression into logical order?>>

  Rushmore works by making merges/intersections between indexes.  JET
queries as we all know are very aware of the underlying tables.  This has
been a key feature of Access/JET since day 1.  JET may be smart enough to
figure out that the logical order is based on two fields it already has
indexes for.  Then again it may not be.

<<How in the world is Access going to determine whether to even try?>>

  It knows the field names and what tables they come from.

<<The resulting string expression can be ANYTHING!!!>>

  Yes, but that wasn't the example Mark gave.

<<Access does NOT use the indexes on the fields used to build up an appended
string.  If you apply a sort order to such a string, Access will do the sort
on the resulting string, on-the-fly, and it will be PAINFULLY slow!!!>>

  That seems to be the case, but do you really know for sure?  I don't
believe you do.  I'm not saying your wrong, but I don't think you can state
it as a fact, unless you have seen docs on JET's internal workings.  That's
why I asked you to cite a source.

  It's just like the myth that got started about domain functions always
being the slowest of methods.  This is not the case as in some situations, a
domain function can actually beat every other method.  That floated around
for several years until someone sat down and tested it and found it not to
be true.

Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Colby
Sent: Thursday, August 14, 2003 10:09 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Indexes on text fields


<<snip>>