I am not very familiar with the SQLite internals, but I believe that index structure is similar to that of a table, ie it's a B-TREE with a root containing a node count value. If so, then queries like SELECT COUNT() FROM FOO WHERE <...>; can be optimised the same way that queries like SELECT COUNT() FROM FOO; given that condition is equivalent tho the condition of the partial index.
Example: sqlite> CREATE TABLE foo( ...> id INTEGER PRIMARY KEY, ...> ref_count INTEGER NOT NULL ...> ); sqlite> sqlite> CREATE INDEX foo_ref_count_idx ON foo(ref_count) WHERE ref_count = 0; sqlite> sqlite> EXPLAIN SELECT COUNT() FROM foo; addr opcode p1 p2 p3 p4 p5 comment ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 0 Init 0 7 0 00 1 OpenRead 1 2 0 1 00 2 Count 1 1 0 00 3 Close 1 0 0 00 4 Copy 1 2 0 00 5 ResultRow 2 1 0 00 6 Halt 0 0 0 00 7 Transactio 0 0 2 0 01 8 TableLock 0 2 0 foo 00 9 Goto 0 1 0 00 sqlite> sqlite> EXPLAIN SELECT COUNT() FROM foo WHERE ref_count = 0; addr opcode p1 p2 p3 p4 p5 comment ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 0 Init 0 13 0 00 1 Null 0 1 1 00 2 OpenRead 1 3 0 k(2,nil,ni 00 3 Integer 0 2 0 00 4 SeekGE 1 8 2 1 00 5 IdxGT 1 8 2 1 00 6 AggStep 0 0 1 count(0) 00 7 Next 1 5 1 00 8 Close 1 0 0 00 9 AggFinal 1 0 0 count(0) 00 10 Copy 1 3 0 00 11 ResultRow 3 1 0 00 12 Halt 0 0 0 00 13 Transactio 0 0 2 0 01 14 TableLock 0 2 0 foo 00 15 Goto 0 1 0 00 sqlite> sqlite> EXPLAIN SELECT COUNT() FROM foo WHERE ref_count != 0; addr opcode p1 p2 p3 p4 p5 comment ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 0 Init 0 13 0 00 1 Null 0 1 1 00 2 OpenRead 0 2 0 2 00 3 Rewind 0 8 0 00 4 Column 0 1 2 00 5 Eq 3 7 2 (BINARY) 54 6 AggStep 0 0 1 count(0) 00 7 Next 0 4 0 01 8 Close 0 0 0 00 9 AggFinal 1 0 0 count(0) 00 10 Copy 1 4 0 00 11 ResultRow 4 1 0 00 12 Halt 0 0 0 00 13 Transactio 0 0 2 0 01 14 TableLock 0 2 0 foo 00 15 Integer 0 3 0 00 16 Goto 0 1 0 00 As we can see 'SELECT COUNT() FROM foo;' query does not scan the table. But 'SELECT COUNT() FROM foo WHERE ref_count = 0;' query does and is no different from 'SELECT COUNT() FROM foo WHERE ref_count != 0;' query that obviously cannot be optimised. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users