Dixon Hutchinson 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 "="
Dixon,
I don't think it matters much one way or the other if you use collate
nocase or like, both involve a function call to determine the result.
You could measure both ways to see which is faster if it is really
important.
I would however be faster if you use the collate nocase option and
change your unique index to reverse the order of the columns. Then
sqlite can use that index to search for the matching strings. As it is
now the index can't be used for a string match because equal values of
the bar are scattered around with different value of p.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
