Igor, Dennis, Richard

Sorry, meant to include a reference to 'p' in my select:
 SELECT * FROM foo WHERE bar LIKE 'something' AND p='some_int';

But that raises the question of the order of the columns within UNIQUE(). Should I put the most "unique" column first, or the thing most easily compared on (ie the integer), or ... What is the common wisdom (that I obviously don't have ;-) ) here. And does using "bar TEXT" vs "bar TEXT COLLATE NOCASE" affect the answer. There probably are not easy answers here as the answer may depend on the character of the data in the table.

My own testing says that in the originally posed case, using "COLLATE NOCASE" is just a titch faster than using LIKE.

D


Igor Tandetnik wrote:
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]
-----------------------------------------------------------------------------


-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------

Reply via email to