On Sun, Apr 15, 2012 at 2:31 PM, George <pinkisntw...@gmail.com> wrote:
> 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? Hard to say why, without knowing your schema. > 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
