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]
-----------------------------------------------------------------------------
