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. 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
