Re: [sqlite] Index usage when querying views
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, SQLite scans through all > involved tables: A select from the view is transformed into SELECT 1 FROM (SELECT * FROM inode_blocks UNION SELECT id as inode, 0 as blockno, block_id FROM inodes WHERE block_id IS NOT NULL ) WHERE inode=42; SQLite's optimizer isn't really that smart - definitely not smart enough to move the condition into the sub-select and duplicate it into each subquery. That's a rather non-trivial transformation. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index usage when querying views
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 the same query on the view, SQLite scans through all > involved tables I think it should work too. Can you create indices on the views ? Hmm. No, I see SQLite doesn't do that. I can only recommend you do both SELECTs and do the same thing with the results of each. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
