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
