Florence Weimar, Igor Tadetnik, Simon Slavin, 
        I ran ANALYZE BLOBLASTNAMETEST in order to get better index statistics. 
        Then I modified my query to: select t1.FieldName,t1.rowid from 
BlobLastNameTest t1
 where t1.rowid = (SELECT MIN(rowid) FROM BlobLastNameTest where FieldName = t1.
FIELDNAME) group by t1.FieldName . 
        It appears that this new query is not doing a full index scan while 
select FieldName, MIN(ROWID) FROM BLOBLASTNAMETEST GROUP BY FIELDNAME is still 
doing a full index scan. The explain query plans are shown below. Thank you.
 
sqlite> explain query plan select t1.FieldName,t1.rowid from BlobLastNameTest t1
 where t1.rowid = (SELECT MIN(rowid) FROM BlobLastNameTest where FieldName = t1.
FIELDNAME) group by t1.FieldName;
0|0|0|SCAN TABLE BlobLastNameTest AS t1 USING COVERING INDEX claramary5 
(~2709783 rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE BlobLastNameTest USING COVERING INDEX claramary5 (FieldName=?
) (~1 rows)
sqlite> explain query plan select FieldName, min(rowid) from BlobLastNameTest gr
oup by FieldName;
0|0|0|SCAN TABLE BlobLastNameTest USING COVERING INDEX claramary5 (~5419566 rows
)
sqlite>                                           
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to