about text search

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

about text search

Lloyd Dupont
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?)
Reply | Threaded
Open this post in threaded view
|

Re: about text search

Puneet Kishor

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

Reply | Threaded
Open this post in threaded view
|

Re: about text search

Derrell Lipman
In reply to this post by Lloyd Dupont
"Lloyd Dupont" <[hidden email]> writes:

> 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?)

Well, you have a couple of problems here (in no particular order):

1. You didn't specify what columns to return in the SELECT statement.

2. You'd be better off being consistent with case.  If you call the table
"Infos" then use the capital 'I' in your query.

3. '*' is not the wildcard token with LIKE; instead you want '%' for wildcard
matches.  LIKE is case-INSENSITIVE in sqlite.  If you want case sensitive,
then you can use GLOB, which uses '*' as the wildcard token.

4. It's best to avoid SQL types as your column names.  'text' isn't the best
choice of column name for this reason.

5. If you are doing '=' comparisons instead of using LIKE then an index on the
field(s) used in the WHERE condition will greatly speed things up.  In sqlite,
LIKE is implemented as a function, and sqlite can not index on functions.

6. The syntax on CREATE TABLE uses parenthesis: '(' ')' not curly braces as
you used: '{' '}'

For what it looks like what you want to do, you probably wanted something like
this:

  CREATE TABLE Infos
  (
      id                INTEGER,
      description       TEXT
  );

  SELECT id, description
    FROM Infos
    WHERE text LIKE '%apple%'
      AND text LIKE '%cinamon%'
      AND text LIKE '%cake%';

or if you know the order that these words will be, then this would faster:

  SELECT id, description
    FROM Infos
    WHERE text LIKE '%apple%cinamon%cake%';

or, for case-sensitive:

  SELECT id, description
    FROM Infos
    WHERE text GLOB '*apple*cinamon*cake*';

Derrell
Reply | Threaded
Open this post in threaded view
|

Re: about text search

Lloyd Dupont
In reply to this post by Puneet Kishor
thanks all!

----- Original Message -----
From: "Puneet Kishor" <[hidden email]>
To: <[hidden email]>
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
>