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. am 
I really stupid ?
 
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to