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
