Hi, all, i know this is probably splitting hairs, and i ask only out of curiosity, not because i'm looking to optimize at this level...
Given a Fossil repository db (namely the event.mtime value, a Julian Day), which of the following is faster for finding the min/max value of that field: SELECT MIN(mtime) FROM event; or: SELECT mtime FROM event ORDER BY mtime LIMIT 1; My intuition says that the first one would be faster, but EXPLAIN tells me that #1 uses 21 ops where #2 uses 16. The EXPLAIN output means nothing to me, though - maybe those 16 represent more work: sqlite> explain SELECT MIN(mtime) FROM event; 0|Trace|0|0|0||00| 1|Null|0|1|2||00| 2|Goto|0|17|0||00| 3|OpenRead|1|3207|0|k(2,nil,nil)|00| 4|Null|0|3|0||00| 5|Affinity|3|1|0|c|00| 6|SeekGt|1|12|3|1|00| 7|Column|1|0|5||00| 8|CollSeq|0|0|0|(BINARY)|00| 9|AggStep|0|5|1|min(1)|01| 10|Goto|0|12|0||00| 11|Next|1|7|0||01| 12|Close|1|0|0||00| 13|AggFinal|1|1|0|min(1)|00| 14|Copy|1|6|0||00| 15|ResultRow|6|1|0||00| 16|Halt|0|0|0||00| 17|Transaction|0|0|0||00| 18|VerifyCookie|0|657|0||00| 19|TableLock|0|2897|0|event|00| 20|Goto|0|3|0||00| (MAX() needs 2 fewer) sqlite> explain SELECT mtime FROM event ORDER BY mtime LIMIT 1; 0|Trace|0|0|0||00| 1|Noop|0|0|0||00| 2|Integer|1|1|0||00| 3|Goto|0|12|0||00| 4|OpenRead|2|3207|0|k(2,nil,nil)|00| 5|Rewind|2|10|2|0|00| 6|Column|2|0|3||00| 7|ResultRow|3|1|0||00| 8|IfZero|1|10|-1||00| 9|Next|2|6|0||01| 10|Close|2|0|0||00| 11|Halt|0|0|0||00| 12|Transaction|0|0|0||00| 13|VerifyCookie|0|657|0||00| 14|TableLock|0|2897|0|event|00| 15|Goto|0|4|0||00| (the MAX variant also needs 16) :-? -- ----- stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
