----- Mensagem original ----- De: "Richard Hipp" <d...@sqlite.org> Para: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Enviadas: Segunda-feira, 31 de Maio de 2010 20:56:33 Assunto: Re: [sqlite] MySQL vs. SQLite
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 About that future release functionality. Will be possible to know whats temporary index are created? Using that information will be easy to know what indexes we need create to increase perfomance, don't giving chance to sqlite create that indexes! -- Regards/Atenciosamente, Israel Lins Albuquerque Developer/Desenvolvimento Polibrás Brasil Software Ltda. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
