Colin Manning <[EMAIL PROTECTED]> wrote:
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:
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;
Prepend each statement with EXPLAIN QUERY PLAN and find out for
yourself.
I believe SQLite is smart enough to optimize this particular usage of
LIKE. If it turns out it isn't after all, change the condition to
a >= 'M' and a < 'N'
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
