On Fri, Mar 14, 2014 at 8:41 AM, Max Vlasov <max.vla...@gmail.com> wrote:
> On Thu, Mar 13, 2014 at 11:06 PM, Richard Hipp <d...@sqlite.org> wrote: > > > > > 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. > > > > > > Actually a comparatively long (10,000,000 elements) CTE for random > integer generation shows difference 20 vs 38 seconds. I suppose pure > min should use linear search while "order by" one uses temporal b-tree > (exlain query also hints about this). Sure unless sqlite has some > detection of "order by limit 1" pattern redirecting it to linear > search. > > In the original problem, there was already an index on the term for which the min() was requested. So with either query, SQLite would merely search for the first non-NULL entry in the index and stop. The specific sequence of VDBE opcodes is slightly different, but both accomplish the same thing: searching for the first non-NULL entry in the index and stopping. Whit your CTE-generated random integers, there is not an index on the values. So "SELECT min(x) FROM..." does a linear search and "SELECT x FROM ... ORDER BY x LIMIT 1" does a sort. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
