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