[sqlite] Index usage when querying views

2011-09-25 Thread Nikolaus Rath
Hello, I have two tables, inodes and inode_blocks, which have indices on id and inode respectively. The following query seems to make optimal use of the indices: sqlite> explain query plan SELECT 1 from inode_blocks where inode=42 UNION SELECT 1 from inodes where block_id

Re: [sqlite] Index usage when querying views

2011-09-25 Thread Simon Slavin
On 25 Sep 2011, at 9:25pm, Nikolaus Rath wrote: > However, if I use an intermediate view: > > sqlite>CREATE VIEW inode_blocks_v AS > SELECT * FROM inode_blocks > UNION > SELECT id as inode, 0 as blockno, block_id FROM inodes WHERE block_id > IS NOT NULL > > and then run t

Re: [sqlite] Index usage when querying views

2011-09-25 Thread Igor Tandetnik
Nikolaus Rath wrote: > However, if I use an intermediate view: > > sqlite>CREATE VIEW inode_blocks_v AS > SELECT * FROM inode_blocks > UNION > SELECT id as inode, 0 as blockno, block_id FROM inodes WHERE block_id > IS NOT NULL > > and then run the same query on the view, SQLit