Colin Manning <[EMAIL PROTECTED]> wrote:
> Hi
>
> If I have a table with a couple of indexed varchar fields e.g:
>
> CREATE TABLE t (id INT, a VARCHAR(20), b VARCHAR(20)...);
> CREATE INDEX ia ON t(a);
> CREATE INDEX ib ON t(b);
>
> then will the sqlite query optimizer use these indices in these SELECT's:
The LIKE cannot be optimized unless you either
A) Set PRAGMA case_sensitive_like=ON;
B) Declare column a to have COLLATE NOCASE.
C) Use "a GLOB 'M*'" instead
>
> 1. SELECT * FROM t WHERE a LIKE 'M%';
> 2. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY a;
> 3. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY b;
> 4. SELECT * FROM t WHERE a LIKE 'M%' ORDER BY b,a;
>
Indices will be used to skip the sorting step in 2 and 3.
If you do this:
DROP INDEX ib;
CREATE INDEX ib ON t(b,a);
Then the new index ib will be used to sort in 4.
If you take the steps A, B, or C above, then the ia
index will be used to both sort and restrict the search
in query 2.
All of this you can discover for yourself by typing
in the query with the following prefix:
EXPLAIN QUERY PLAN
--
D. Richard Hipp <[EMAIL PROTECTED]>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
