"Igor Tandetnik" <[EMAIL PROTECTED]> wrote:
> 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 is mostly true, but there are exceptions. LIKE might
use an index if:
(1) The left-hand side is an indexed column.
(2) The like() function has not been overloaded.
(3) Case-sensitive LIKE is turned on OR the left-hand side
column is COLLATE NOCASE
(4) The right-hand side is a literal string, not an expression,
or parameter, or column.
(5) The first character of the literal string on the right-hand
side is not a wildcard.
If all of the above are true (and as it happens, they are all true
in the example above, assuming the use of COLLATE NOCASE on the
foo.bar column) then an index will be used to speed up the LIKE.
Actually, the LIKE term itself is unaltered and does not itself
use an index. But the presense of the LIKE causes two new "virtual"
terms to be added to the WHERE clause like this:
... AND bar>='something' AND bar<'somethinh'
And these two virtual terms might well use an index.
So even though LIKE does not itself use an index, it sort of
provokes the use of an index indirectly.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
