"Lloyd Dupont" <[EMAIL PROTECTED]> 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
