Igor Tandetnik, Here is a comparison of my query plan with your query plan
on the latest version of sqlite.
sqlite> explain query plan select FieldName, min(rowid) from BlobLastNameTest
group by FieldName;
0|0|0|SCAN TABLE BlobLastNameTest USING COVERING INDEX claramary (~1000000 rows)
sqlite> explain query plan select t1.FieldName,t1.rowid from BlobLastNameTest t1
GROUP BY t1.FIELDNAME HAVING t1.rowid = (SELECT MIN(rowid) FROM BlobLastNameTes
t where FieldName = t1.FIELDNAME);
0|0|0|SCAN TABLE BlobLastNameTest AS t1 USING COVERING INDEX claramary (~1000000
rows)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE BlobLastNameTest USING COVERING INDEX claramary (FieldName=?)
(~1 rows)
Your query plan apparently traverses the claramary index(CREATE INDEX claramary
ON BlobLastNameTest(FieldName)) on the whole table(which may have side effects
as the number of rows on my table grows from 2.7 million to 20 million) while
my query plan also tries to execute correlated subquery which exploits the
CREATE INDEX claramary ON BlobLastNameTest(FieldName). So, why is my query just
working accidently? Thank you.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
