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
[sqlite] Index usage when querying views
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 is not null and id=42; 1|0|0|SEARCH TABLE inode_blocks USING COVERING INDEX sqlite_autoindex_inode_blocks_1 (inode=?) (~5 rows) 2|0|0|SEARCH TABLE inodes USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION) 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: sqlite> explain query plan SELECT 1 FROM inode_blocks_v WHERE inode=42; 2|0|0|SCAN TABLE inode_blocks (~10711 rows) 3|0|0|SCAN TABLE inodes (~131030 rows) 1|0|0|COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION) 0|0|0|SCAN SUBQUERY 1 (~14174 rows) Is there any way I can make SQLite use the indices here as well? I can't see any reason of why they couldn't be used. Best, -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
