[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] about text search
From:       "Lloyd Dupont" <ld () galador ! net>
Date:       2005-06-25 23:32:38
Message-ID: 003901c579de$2cbe8070$27f731d2 () vulcain
[Download RAW message or body]

thanks all!

----- Original Message ----- 
From: "Puneet Kishor" <pkishor@eidesis.org>
To: <sqlite-users@sqlite.org>
Sent: Sunday, June 26, 2005 9:21 AM
Subject: Re: [sqlite] about text search


> 
> On Jun 25, 2005, at 6:05 PM, Lloyd Dupont wrote:
> 
>> let's say I have a table
>> like that
>> CREATE TABLE Infos
>> {
>>     id INTEGER,
>>     text TEXT
>> }
>>
>> and I want to search a with the word... 'apple', 'cinamon', 'cake'
>> I could write
>> SELECT FROM infos WHERE text LIKE '*apple*' AND text LIKE '*cinamon*' 
>> AND text LIKE '*cake*'
>>
>> Now, isn't there a way to improve anything (with index or otherwise?)
> 
> akaik, LIKE doesn't use indexes. So, you are stuck with a full table 
> scan. That said, the usual SQL is using % instead of * for wildcards. 
> And, your above statement is looking for all the records that contain 
> all of the above three words. If you want all the records with any of 
> the 3 words then you should use OR instead of AND.
> 
> If the table is really big, another way would be to dump the records 
> out from the table and then use a regular text search engine such as 
> Swish-e, Lucene, or Plucene.
> 
> --
> Puneet Kishor
>
[prev in list] [next in list] [prev in thread] [next in thread]