Re: [sqlite] Is it possible to optimize this query on a very large datatabase table
Frank Chang wrote: > So, why is my query just working accidently? I take it back - the behavior of your query is well-defined, for the simple reason that FieldName is unique, so every group only has a single row in it (as someone else has kindly pointed out - I missed this detail on the first reading). For that same reason, the whole GROUP BY and sub-select dance is completely pointless. Your query is just a very elaborate and wasteful way to write select FieldName, rowid from BlobLastNameTest; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to optimize this query on a very large datatabase table
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 (~100 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 (~100 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
