Dixon Hutchinson
<sqlite-491LjFE5FnHkPponyUOvwgC/[EMAIL PROTECTED]> wrote:
Sorry, meant to include a reference to 'p' in my select:
SELECT * FROM foo WHERE bar LIKE 'something' AND p='some_int';
An expression involving LIKE cannot use an index anyway. This query
would use the index to drill down to the rows having p=some_int, then
scan those sequentially.
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 ...
This depends on the queries you expect to speed up. If you always use
both columns in the condition (in a way that can use index) then I
believe the order does not matter. If you also need to run queries
putting a condition on just one field, put this field first.
And does using "bar
TEXT" vs "bar TEXT COLLATE NOCASE" affect the answer.
Not really, as long as comparisons in the query condition match the way
the index was built.
My own testing says that in the originally posed case, using "COLLATE
NOCASE" is just a titch faster than using LIKE.
Run EXPLAIN QUERY PLAN on your query, see if the index is used.
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
