On Thu, 2007-07-19 at 22:56 +0100, Colin Manning 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: > > 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;
I think all of those queries will use index "ia". For queries 3 and 4, a subset of table t will be scanned, and a temporary b-tree structure used to do the ORDER BY. Dan. > ...such that none of them will result in a table scan? > > Thx > > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
