On 09/23/2011 04:01 AM, Mira Suk wrote:
EXPLAIN QUERY PLAN SELECT DISTINCT ItemsME.Points FROM (ItemsME_Properties LEFT JOIN ItemsME ON ItemsME_Properties.IDR = ItemsME.IDR) WHERE ItemsME.IDR IN (SELECT IDR FROM cProds WHERE Prod = 106) selectid order from detail 0 0 0 SCAN TABLE ItemsME_Properties (~1000000 rows) 0 1 1 SEARCH TABLE ItemsME USING INDEX IDR (IDR=?) (~2 rows) 0 0 0 EXECUTE LIST SUBQUERY 1 1 0 0 SEARCH TABLE cProds USING INDEX Prod (Prod=?) (~210 rows) 0 0 0 EXECUTE LIST SUBQUERY 2 2 0 0 SEARCH TABLE cProds USING INDEX Prod (Prod=?) (~210 rows) 0 0 0 USE TEMP B-TREE FOR DISTINCT this takes about 3 seconds EXPLAIN QUERY PLAN SELECT DISTINCT ItemsME.Points FROM (ItemsME_Properties INNER JOIN ItemsME ON ItemsME_Properties.IDR = ItemsME.IDR) WHERE ItemsME.IDR IN (SELECT IDR FROM cProds WHERE Prod = 106) 0 0 1 SEARCH TABLE ItemsME USING INDEX IDR (IDR=?) (~25 rows) 0 0 0 EXECUTE LIST SUBQUERY 1 1 0 0 SEARCH TABLE cProds USING INDEX Prod (Prod=?) (~210 rows) 0 1 0 SEARCH TABLE ItemsME_Properties USING COVERING INDEX IDR (IDR=?) (~1 rows) 0 0 0 USE TEMP B-TREE FOR DISTINCT this runs in about 15 milisec => results are equal from both cases as ItemsME_Properties is temporary table (updated with triggers as reads are much more frequent) to workaround performance issues with search... (it should be 1:1 with ItemsME, just with less data, and some data reformatted) maybe I'm just stupid but I don't really see reason for scan in first case.
Basically it is because with an inner join SQLite can reorder the tables in the join - "A INNER JOIN B" is the same as "B INNER JOIN A". But it can't do that with left joins - as "A LEFT JOIN B ON <expr>" is not always the same as "B LEFT JOIN A ON <expr>". And in this case SQLite needs to reorder tables ItemsME and ItemsME_Properties in order to avoid the table scan. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
