Please Explain - Index by Word

[Ruslan Zasukhin]

On 7/25/14, 3:55 AM, "Kay C Lan" <lan.kc.macmail at gmail.com> wrote:

Hi Kay,

> The online information is relatively brief and I understand the size
> issues if you choose to enable 'Index by Word', but I'm wondering if
> there any other advantages and disadvantages, when it should and
> shouldn't be used, and any special 'features' of how to use it.

 
> In particular I'm in the process of creating a very small database.
> One of the tables = customers, and has fields = fullname, email,
> phone.
> 
> I've entered only 1 record for testing purposes: John Doe,
> johndoe at email.com, 555 5555-5555

 
> With 'index by word' selected if I
 
> SELECT fullname,email FROM customer WHERE fullname = 'John Doe'
> 
> Nothing is returned and I get this in the Warning pane:

Yes, because index by words looks as
        Value       RecID
        John        1
        Doe         1
         
So 'John Doe' is not found


> Index-By_Words search : FindValue customers::fullname
> 
> If I change the search from = to LIKE:
> 
> SELECT fullname,email FROM customer WHERE fullname LIKE 'John Doe'
> 
> I get what I originally expected

Because  COLUMN itself is scanned without index.

 
> John Doe, johndoe at email.com
> 
> If I turn 'index by Word' off and do the original = search I get
> 
> John Doe, johndoe at email.com
> 
> Is that the correct output.

> If I use 'Index by Words' should I not use
> = but use LIKE for my SELECT statements even if I'm looking for an
> exact match?


Index By Words is good choice for big strings,
For example 'Body of email'.


IndexByWords is not so powerful as Full Text search,
we do not have it. But usually this is enough.



-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]