[V4RB2] VText search speed

[Ruslan Zasukhin]

On 4/25/05 2:08 AM, "Kem Tekinay" <ktekinay at mactechnologies.com> wrote:

Hi Kem,

> I just installed 2.0.3fc4 and one of the benefits is that the initial search
> of a VText field is MUCH faster. Before, a FindContains search of the VText
> field of 57,000 records, indexed by words, would take about 20 seconds
> initially, then drop to about 2.5 seconds. Now even the initial search takes
> about 2.5 seconds. Excellent.
> 
> But I am very greedy. My database is of about 40,000 e-mails. I record the
> To, From, and Subject as VarChar in the main record. The actual text of the
> e-mail is in a related table linked as BinaryObject. Each e-mail can have up
> to three different texts associated with it: Plain, Enriched and HTML. Each
> one of those is a separate record in the related table.
> 
> So here's my question: It takes less than 0.25 seconds to do a FindContains
> search the VarChar fields (To, From or Subject) that are just indexed. It
> takes 2.5 seconds to do a FindContains search the VText field of the actual
> messages (about 57,000 records). But since the VText field is indexed as
> words, I would expect it to be just as fast as the VarChar search, if not
> faster. Am I dreaming?

It needs study more deep your db to say something.
 
> Also, is there a way to force a search of a phrase on a field that is
> indexed by words? For example, I can search for "and" and "the", but "and
> the" yields no records even though they have to be there.

You should have OR second index without ByWords flag.
You can do this using Table Methods.

Or you can use LIKE search

-- 
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]