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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users