on 2/27/2001 2:26 PM, Ruslan Zasukhin <sunshine At public D.OT kherson.ua> wrote:
> on 27/2/01 20:20, Paul Scandariato at paul_lists@intellisw.com wrote:
>
>> I've been doing some heavy work in V4RB in the past few days, and I've run
>> into 2 very basic questions relating to indexes.
>>
>> 1. I have a series of VarChar fields that I'm searching using LIKE (i.e.
>> SELECT * from TableName WHERE myvarchar LIKE "Paul"). If I understand the
>> Kernel reference correctly, indexing VarChar fields (either generally or by
>> words) doesn't help if I'm using LIKE. Is this correct? If so, how can I
>> modify my queries so that they that take advantage of my indexes?
>
> 1) WHERE myvarchar = 'Paul'
>
> 2) WHERE left(myvarchar,4 ) = 'Paul'
>
> left function is START WITH search and it use index also.
Working well - guess I'll be using IndexByWords on all my VarChars now. :-)
>
>> 2. I'm storing a series of e-mail messages in a database. There are several
>> VarChar fields (to and from sorts of things), a DateTime field, and subject
>> and body fields. The subject field is a VarChar(255), while the body is a
>> Text (VText) field. After putting about 21,000 messages into the database, I
>> ran a search for a word (using LIKE) in the Text field - it took about 6
>> seconds, and it came back with about 20 matching records.
>>
>> I ran the same test in a simple FileMaker Pro database, using the same data
>> (at the request of my client for benchmark tests). I searched the body field
>> there, and it was practically instantaneous. Obviously, I fully expect
>> Valentina to outperform FileMaker, so my only conclusion is that I'm making
>> a mistake somewhere along the line. Should I be using a 32K or 64K VarChar
>> for this (isn't that a bit big for a VarChar?), or is there something else I
>> can do to speed up the search using the Text field I'm using now?
>
> I can bet in FM you have had index.
>
> so to get the same in Valentina you should set for VText findexByWords
> and use search:
>
> where txtField = 'word'
>
> see the difference :-)
Wow. That's fantastic. I doubled over the messages for testing, and
Valentina's search speed was VERY impressive. Thanks Ruslan!
- Paul
--
Paul Scandariato
Intelli Innovations, Inc.
http://www.intellisw.com/
E-mail: paul At intellisw D.OT com
It is impossible to get anywhere
without sinning against reason.
- Albert Einstein
©2001 Paul Scandariato |
