Dixon Hutchinson
<sqlite-491LjFE5FnHkPponyUOvwgC/[EMAIL PROTECTED]> wrote:
I have an SQLite db with one TEXT column. That column and an INTEGER
column (not the rowid as this column is definitely not unique by
itself) are declared UNIQUE together.
CREATE TABLE foo (
bar TEXT,
p INTEGER,
rowid INTEGER PRIMARY KEY AUTOINCREMENT, -- I know... this is
implicit...
UNIQUE(p, bar)
);
All of my searches of the db when searching by the TEXT column must be
case insensitive. So I have been using: SELECT * FROM foo WHERE bar
LIKE 'some text here';
Is that better, worse, or no different than using a "COLLATE NOCASE"
qualifier on the TEXT column and changing the "LIKE" to "="
Equality test is probably going to be somewhat faster than LIKE test in
any case, but don't expect any dramatic time savings. Since your query
does not use p, and p is the first field in the index, the index will
not be used whether you use = or LIKE. If the statement you show is a
common one and you are concerned about its performance, put COLLATE
NOCASE on the bar column and also change the constraint to UNIQUE(bar,
p) (or else create a separate index on bar).
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
