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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
