On Wed, May 7, 2014 at 4:51 PM, Hinrichsen, John <jhinrich...@c10p.com>wrote:
> $ sqlite3 > SQLite version 3.7.17 2013-05-20 00:56:22 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> CREATE TABLE x AS SELECT 1 AS a, 1 AS b; > sqlite> CREATE INDEX ix ON x (a); > sqlite> CREATE TABLE y AS SELECT 1 AS b; > sqlite> EXPLAIN QUERY PLAN SELECT * FROM x INNER JOIN y ON x.b=y.b; > 0|0|0|SCAN TABLE x (~1000000 rows) > 0|1|1|SEARCH TABLE y USING AUTOMATIC COVERING INDEX (b=?) (~7 rows) > sqlite> EXPLAIN QUERY PLAN SELECT * FROM x INNER JOIN y ON x.b=y.b WHERE > x.a = 1; > 0|0|0|SEARCH TABLE x USING INDEX ix (a=?) (~10 rows) > 0|1|1|SEARCH TABLE y USING AUTOMATIC COVERING INDEX (b=?) (~7 rows) > sqlite> > > $ sqlite3 > SQLite version 3.8.4.3 2014-04-03 16:53:12 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> CREATE TABLE x AS SELECT 1 AS a, 1 AS b; > sqlite> CREATE INDEX ix ON x (a); > sqlite> CREATE TABLE y AS SELECT 1 AS b; > sqlite> EXPLAIN QUERY PLAN SELECT * FROM x INNER JOIN y ON x.b=y.b; > 0|0|0|SCAN TABLE x > 0|1|1|SEARCH TABLE y USING AUTOMATIC COVERING INDEX (b=?) > sqlite> EXPLAIN QUERY PLAN SELECT * FROM x INNER JOIN y ON x.b=y.b WHERE > x.a = 1; > 0|0|0|SEARCH TABLE x USING INDEX ix (a=?) > 0|1|1|SCAN TABLE y > sqlite> > Do you have a database file where the 3.8.4.3 query plan really is slower? Can you please run ANALYZE on that database and send us the content of the "sqlite_stat1" table? -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
