On Thu, Mar 13, 2014 at 2:37 PM, Stephan Beal <sgb...@googlemail.com> wrote:
> 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: > The output of EXPLAIN looks much nicer if you do ".explain" first to set up appropriate formatting. Once you do that, you'll see that the opcode sequence is only slightly different between the two. They should both run at about the same speed. I doubt you'll be able to measure the difference. > > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
