Re: [sqlite] MySQL vs. SQLite
On Mon, May 31, 2010 at 3:57 PM, Simon Slavin 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
[sqlite] MySQL vs. SQLite
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
Re: [sqlite] sqlite-users Digest, Vol 29, Issue 29
Thanks for many excellent suggestions on how to speed up the query. I can now make sqlite3 essentially tie mysql. (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?) > You also need to increase your cache size to match the mysql performance > pragma cache 10; This helped. Thanks. The other suggestions involved indexing on more than one field. These are excerpted below, and these all helped. Thanks again. Best, Michael >>> sqlite> explain query plan >>> ? ...> SELECT name,score >>> ? ...> FROM facility f, (SELECT facilitynumber,SUM(score_rev) AS score >>> ? ...> ? ? ? ? FROM release_cl >>> ? ...> ? ? ? ? WHERE media<3 >>> ? ...> ? ? ? ? AND year=2006 >>> ? ...> ? ? ? ? GROUP BY facilitynumber) r >>> >> Michael, from what I see, if your release_cl table is not properly indexed >> to be quickly aggregated (media and year field), this will lead to full >> table reading (so all the data of your 4M records). If it's properly >> indexed, and the result number of records of this select is big, consider >> adding non-indexed fields to this (or brand-new) index since otherwise >> sqlite quickly finds records with this index, but have to look up main data >> tree to retrieve other fields. > How many distinct media-types are there? > How many distinct facilities do you have? > How many rows are typically returned by your FacilityScore subquery? There are about 30 distinct media and about 18 years. There are 50,000 distinct facilities. The FacilityScore subquery returns about 16,000 rows. > Per suggestions, I indexed year and media on the big table. So I now > have separate indexes for the key variable (releasenumber) and for > year and for media.Would it make more sense to have a single > index for all three, thus: > > CREATE UNIQUE INDEX r ON release_cl (year,media,releasenumber); Correct. Each SELECT command uses zero or one index. It's pointless to create individual indexes for each column in your table. Look at the columns, ORDER BY and WHERE of your SELECT query and make up one index which is ideal for that query. For the ideal index in SQLite, you make the index up of first, the columns mentioned in the WHERE clause then, the columns mentioned in the ORDER BY clause finally any other columns you want retrieved. For each purpose you have to choose your preferred cutoff: it's usually not worth doing the last section of the above, and sometimes not even worth doing the ORDER BY section. The more indexes, and the longer they are, the longer each WRITE operation takes for that table. You could end up with a table which might be fast to search but need a very long time for each INSERT. Also, increasing the length of each index will increase the database filesize, and therefore cause operations which change the database file to take longer. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Sample Code [Linked]
On 31 May 2010, at 12:15pm, Black, Michael (IS) wrote: > could I be so bold as to recommend you complete it with BUSY and LOCKED > possibilities? Michael returns to a subject I would love someone to put some time into, but don't know enough to do myself. I would like to see a fuller treatment of _BUSY and _LOCKED statuses with explanations and pseudocode and options. Perhaps broken down by function, or perhaps just one for prepare->bind->step and one for the stand-alone functions which don't involve steps. Most errors generated by SQLite are quite easy to handle: either they're fatal and your program should quit with an error message, or they're things you're intentionally testing so your program would obviously have code in to deal with them. But it's not obvious to me how to deal with _BUSY and _LOCKED. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Sample Code [Linked]
Nice example Gary...pretty clean. But...could I be so bold as to recommend you complete it with BUSY and LOCKED possibilities? Man will likely use your code as-is. I remember a long time ago seeing some examples in a computer programming book by Knuth or such in my college classes. Along with the example was the statement "this is far from optimal". And yet...you could find that code in so many places being used to write lousy software. So I'm a HUGE fan of "complete" examples so that others don't abuse the code. Thanks a lot for the effort to 'splain things... Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Gary Briggs Sent: Sun 5/30/2010 7:29 PM To: sqlite-users@sqlite.org Subject: [sqlite] Simple Sample Code [Linked] Heya All, I've been a long-time lurker on the irc channel, helping where I can. One question that constantly pops up is "what's the sqlite equivalent of mysql_real_escape_string?" One doesn't like to necessarily answer that particular question as posed, so up until now I've been explaining each time how to use prepare->bind->step; the examples on the wiki either don't demonstrate the point [http://www.sqlite.org/cvstrac/wiki?p=SimpleCode], or are a lot more complex than necessary [http://www.sqlite.org/cvstrac/wiki?p=SampleCode] I've written what I think is a lot simpler/clearer example here: http://icculus.org/~chunky/stuff/sqlite3_example/sqlite3_example_bind.c That code pretty much answers 3/4 of the questions I regularly see on IRC. Not sure if that's really of interest to anyone, but if it is, great! Thanks, Gary (-; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Simple Sample Code [Linked]
Heya All, I've been a long-time lurker on the irc channel, helping where I can. One question that constantly pops up is "what's the sqlite equivalent of mysql_real_escape_string?" One doesn't like to necessarily answer that particular question as posed, so up until now I've been explaining each time how to use prepare->bind->step; the examples on the wiki either don't demonstrate the point [http://www.sqlite.org/cvstrac/wiki?p=SimpleCode], or are a lot more complex than necessary [http://www.sqlite.org/cvstrac/wiki?p=SampleCode] I've written what I think is a lot simpler/clearer example here: http://icculus.org/~chunky/stuff/sqlite3_example/sqlite3_example_bind.c That code pretty much answers 3/4 of the questions I regularly see on IRC. Not sure if that's really of interest to anyone, but if it is, great! Thanks, Gary (-; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [ODBC] - possible Bug
[...] The solution is: mark "Benannte Parameter durch '?' ersetzen" (translation: "Replace named parameter with '?'" in OpenOffice Base Thanks to Christian Werner ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
