Compare the following two queries and their query plans: 1) explain query plan select * from snapshotsmarketsrunners where marketsru nnersid in (10000);
0|0|0|SEARCH TABLE snapshotsmarketsrunners USING INDEX SnapshotsMarketsRunnersMa rketsRunnersIDSnapshotsID (MarketsRunnersID=?) (~10 rows) 0|0|0|EXECUTE LIST SUBQUERY 1 2) explain query plan select * from snapshotsmarketsrunners where marketsru nnersid in (select id from marketsrunners where marketsid = 105195390); 0|0|0|SCAN TABLE snapshotsmarketsrunners (~223868 rows) 0|0|0|EXECUTE LIST SUBQUERY 1 1|0|0|SEARCH TABLE marketsrunners USING COVERING INDEX MarketsRunnersMarketsID ( MarketsID=?) (~10 rows) Why is the index used in the first query but not in the second? The same thing happens if I rewrite query (2) using JOIN syntax: explain query plan select * from snapshotsmarketsrunners join marketsrun ners on marketsrunnersid = marketsrunners.id where marketsid = 105195390; 0|0|0|SCAN TABLE snapshotsmarketsrunners (~2238685 rows) 0|1|1|SEARCH TABLE marketsrunners USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
