thanks all!
----- Original Message -----
From: "Puneet Kishor" <[EMAIL PROTECTED]>
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
