Yep, index on columns in the where close in the order they appear solved this problem. In fact, it was twice as fast as sql server.
On Tue, Aug 16, 2011 at 4:47 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 16 Aug 2011, at 10:05pm, Yonnas Beyene wrote: > > > SELECT distinct Table1.Column1, Table2.Column2, Table1.Column2 > > FROM ( Table1 INNER JOIN Table2 ON Table1.Column1 = Table2.Column1) > > WHERE ( Table1.PrimaryKeyID =?) And ( Table1.Column1 IS NOT NULL) > > > > The explain query plan looks like, > > > > 0 0 0 SEARCH TABLE Table1 USING AUTOMATIC COVERING INDEX (PrimaryKeyID=?) > > (~7 rows) > > 0 1 1 SEARCH TABLE Table2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) > > 0 0 0 USE TEMP B-TREE FOR DISTINCT > > > > without the distinct this query runs very fast. would adding an index on > > all columns used in the select will speed this query? > > I don't know what indexes you already have on those tables, but you should > definitely be able to find an index on each table that makes that SELECT run > faster. I would try something like > > CREATE INDEX T1PKIC1C2 on Table1 (PrimaryKeyID, Column1, Column2) > CREATE INDEX T2C1C2 on Table2 (Column1, Column2) > > and see if they help. If so, you might be able to find better or worse > ones. If they make no difference at all, please post again. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
