Florian Weimar and Igor Tadetnik, When I replace the GROUP BY t1.FIELDNAME with ORDER BY 1, select t1.FieldName,t1.rowid from BlobLastNameTest t1 where t1.r owid = (SELECT MIN(rowid) FROM BlobLastNameTest where FieldName = t1.FIELDNAME) order by 1;
the explain output seems to have 40% less steps. Does this mean order by 1 should be faster than group by t1.FIELDNAME as I scale up the number of rows in the very large database table BLOBLASTNAMETEST. The explain output is ahown below. Thank you. sqlite> explain select t1.FieldName,t1.rowid from BlobLastNameTest t1 where t1.r owid = (SELECT MIN(rowid) FROM BlobLastNameTest where FieldName = t1.FIELDNAME) order by 1; 0|Trace|0|0|0||00| 1|Noop|0|0|0||00| 2|Goto|0|32|0||00| 3|OpenRead|3|108789|0|keyinfo(1,BINARY)|00| 4|Rewind|3|30|1|0|00| 5|IdxRowid|3|1|0||00| 6|Null|0|3|0||00| 7|Integer|1|4|0||00| 8|Null|0|6|0||00| 9|Null|0|5|0||00| 10|OpenRead|4|108789|0|keyinfo(1,BINARY)|00| 11|Column|3|0|7||00| 12|IsNull|7|20|0||00| 13|SeekGe|4|20|7|1|00| 14|IdxGE|4|20|7|1|01| 15|IdxRowid|4|9|0||00| 16|CollSeq|0|0|0|collseq(BINARY)|00| 17|AggStep|0|9|5|min(1)|01| 18|Goto|0|20|0||00| 19|Next|4|14|0||00| 20|Close|4|0|0||00| 21|AggFinal|5|1|0|min(1)|00| 22|SCopy|5|10|0||00| 23|Move|10|3|1||00| 24|IfZero|4|25|-1||00| 25|Ne|3|29|1||6c| 26|Column|3|0|11||00| 27|IdxRowid|3|12|0||00| 28|ResultRow|11|2|0||00| 29|Next|3|5|0||00| 30|Close|3|0|0||00| 31|Halt|0|0|0||00| 32|Transaction|0|0|0||00| 33|VerifyCookie|0|7|0||00| 34|TableLock|0|2|0|BlobLastNameTest|00| 35|Goto|0|3|0||00| sqlite> sqlite> explain select t1.FieldName,t1.rowid from BlobLastNameTest t1 where t1.r owid = (SELECT MIN(rowid) FROM BlobLastNameTest where FieldName = t1.FIELDNAME) GROUP by t1.FieldName; 0|Trace|0|0|0||00| 1|Noop|0|0|0||00| 2|Integer|0|4|0||00| 3|Integer|0|3|0||00| 4|Gosub|6|51|0||00| 5|Goto|0|55|0||00| 6|OpenRead|3|108789|0|keyinfo(1,BINARY)|00| 7|Rewind|3|40|9|0|00| 8|IdxRowid|3|9|0||00| 9|Null|0|11|0||00| 10|Integer|1|12|0||00| 11|Null|0|14|0||00| 12|Null|0|13|0||00| 13|OpenRead|4|108789|0|keyinfo(1,BINARY)|00| 14|Column|3|0|15||00| 15|IsNull|15|23|0||00| 16|SeekGe|4|23|15|1|00| 17|IdxGE|4|23|15|1|01| 18|IdxRowid|4|17|0||00| 19|CollSeq|0|0|0|collseq(BINARY)|00| 20|AggStep|0|17|13|min(1)|01| 21|Goto|0|23|0||00| 22|Next|4|17|0||00| 23|Close|4|0|0||00| 24|AggFinal|13|1|0|min(1)|00| 25|SCopy|13|18|0||00| 26|Move|18|11|1||00| 27|IfZero|12|28|-1||00| 28|Ne|11|39|9||6c| 29|Column|3|0|8||00| 30|Compare|7|8|1|keyinfo(1,BINARY)|00| 31|Jump|32|36|32||00| 32|Move|8|7|1||00| 33|Gosub|5|45|0||00| 34|IfPos|4|54|0||00| 35|Gosub|6|51|0||00| 36|Column|3|0|1||00| 37|IdxRowid|3|2|0||00| 38|Integer|1|3|0||00| 39|Next|3|8|0||00| 40|Close|3|0|0||00| 41|Gosub|5|45|0||00| 42|Goto|0|54|0||00| 43|Integer|1|4|0||00| 44|Return|5|0|0||00| 45|IfPos|3|47|0||00| 46|Return|5|0|0||00| 47|Copy|1|19|0||00| 48|Copy|2|20|0||00| 49|ResultRow|19|2|0||00| 50|Return|5|0|0||00| 51|Null|0|1|0||00| 52|Null|0|2|0||00| 53|Return|6|0|0||00| 54|Halt|0|0|0||00| 55|Transaction|0|0|0||00| 56|VerifyCookie|0|7|0||00| 57|TableLock|0|2|0|BlobLastNameTest|00| 58|Goto|0|6|0||00| sqlite> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
