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

Reply via email to