On Mon, May 31, 2010 at 3:57 PM, Simon Slavin <slav...@bigfraud.org> wrote:
> > On 31 May 2010, at 7:17pm, Michael Ash wrote: > > > (Curiously, this is only > > the case on the first run of the query; if I run the query again, the > > MySQL time drops to close to zero while sqlite appears to take the > > same time. Maybe MySQL leaves the database sorted or somehow caches > > that particular run?) > > SQLite finds the best index it can and uses it. If there are no indices, > it searches the entire table. > > MySQL has a server process which runs until you shut it down (usually when > you reboot the server). Every time you do anything that could use an index > (including the WHERE clause in an UPDATE) it looks for a perfect index. If > it finds one, it uses it. If it doesn't find one, it finds the best it can, > but constructs a temporary index specially designed for the operating you're > doing. These temporary indices are cached, on the assumption that if you've > executed a command once, you're probably going to do it again. > > This is an excellent part of MySQL and has lead many MySQL programmers to > completely ignore the CREATE INDEX command because once MySQL has executed > one of every command, everything executes quickly. However, it requires a > lot of memory to be used for caching, and a persistent server process. And > it would require a thorough rewrite of SQLite which would then no longer be > practical for small fast embedded devices. > Great explanation, Simon. Thanks! FWIW, the latest versions of SQLite in the source tree will also create a temporary index to help with a query, if SQLite estimates that the expense of creating and using index is less than doing a full-table scan. SQLite is unable to cache indices, though. So the entire cost of building the index must be recouped on a single query or SQLite will figure that creating the index is not worth the effort and just go ahead with a brute-force query. Hence, temporary indices are normally only created for multi-way joins or perhaps for subqueries. This automatic-indexing feature is new. It has only been in the source tree since early April and has not yet appeared in a released version of SQLite. > > MySQL and SQLite are both excellent examples of their craft, but they're > suitable for different situations. About the only thing they have in common > is that they both speak SQL. > > Simon. > _______________________________________________ > 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
