Debian Codegraph @

www/speed.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>SQLite Database Speed Comparison</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118
119
120 <h2>Database Speed Comparison</h2>
121
122 <font color="red"><b>
123 Note: This document is very very old. It describes a speed comparison between
124 archaic versions of SQLite, MySQL and PostgreSQL.
125 <p>
126 The numbers here have become meaningless. This page has been retained only
127 as an historical artifact.
128 </b></font>
129
130 <h3>Executive Summary</h3>
131
132 <p>A series of tests were run to measure the relative performance of
133 SQLite 2.7.6, PostgreSQL 7.1.3, and MySQL 3.23.41.
134 The following are general
135 conclusions drawn from these experiments:
136 </p>
137
138 <ul>
139 <li><p>
140 SQLite 2.7.6 is significantly faster (sometimes as much as 10 or
141 20 times faster) than the default PostgreSQL 7.1.3 installation
142 on RedHat 7.2 for most common operations.
143 </p></li>
144 <li><p>
145 SQLite 2.7.6 is often faster (sometimes
146 more than twice as fast) than MySQL 3.23.41
147 for most common operations.
148 </p></li>
149 <li><p>
150 SQLite does not execute CREATE INDEX or DROP TABLE as fast as
151 the other databases. But this is not seen as a problem because
152 those are infrequent operations.
153 </p></li>
154 <li><p>
155 SQLite works best if you group multiple operations together into
156 a single transaction.
157 </p></li>
158 </ul>
159
160 <p>
161 The results presented here come with the following caveats:
162 </p>
163
164 <ul>
165 <li><p>
166 These tests did not attempt to measure multi-user performance or
167 optimization of complex queries involving multiple joins and subqueries.
168 </p></li>
169 <li><p>
170 These tests are on a relatively small (approximately 14 megabyte) database.
171 They do not measure how well the database engines scale to larger problems.
172 </p></li>
173 </ul>
174
175 <h3>Test Environment</h3>
176
177 <p>
178 The platform used for these tests is a 1.6GHz Athlon with 1GB or memory
179 and an IDE disk drive. The operating system is RedHat Linux 7.2 with
180 a stock kernel.
181 </p>
182
183 <p>
184 The PostgreSQL and MySQL servers used were as delivered by default on
185 RedHat 7.2. (PostgreSQL version 7.1.3 and MySQL version 3.23.41.)
186 No effort was made to tune these engines. Note in particular
187 the default MySQL configuration on RedHat 7.2 does not support
188 transactions. Not having to support transactions gives MySQL a
189 big speed advantage, but SQLite is still able to hold its own on most
190 tests.
191 </p>
192
193 <p>
194 I am told that the default PostgreSQL configuration in RedHat 7.3
195 is unnecessarily conservative (it is designed to
196 work on a machine with 8MB of RAM) and that PostgreSQL could
197 be made to run a lot faster with some knowledgeable configuration
198 tuning.
199 Matt Sergeant reports that he has tuned his PostgreSQL installation
200 and rerun the tests shown below. His results show that
201 PostgreSQL and MySQL run at about the same speed. For Matt's
202 results, visit
203 </p>
204
205 <blockquote>
206 Obsolete URL: http://www.sergeant.org/sqlite_vs_pgsync.html
207 </blockquote>
208
209 <p>
210 SQLite was tested in the same configuration that it appears
211 on the website. It was compiled with -O6 optimization and with
212 the -DNDEBUG=1 switch which disables the many "assert()" statements
213 in the SQLite code. The -DNDEBUG=1 compiler option roughly doubles
214 the speed of SQLite.
215 </p>
216
217 <p>
218 All tests are conducted on an otherwise quiescent machine.
219 A simple Tcl script was used to generate and run all the tests.
220 A copy of this Tcl script can be found in the SQLite source tree
221 in the file <b>tools/speedtest.tcl</b>.
222 </p>
223
224 <p>
225 The times reported on all tests represent wall-clock time
226 in seconds. Two separate time values are reported for SQLite.
227 The first value is for SQLite in its default configuration with
228 full disk synchronization turned on. With synchronization turned
229 on, SQLite executes
230 an <b>fsync()</b> system call (or the equivalent) at key points
231 to make certain that critical data has
232 actually been written to the disk drive surface. Synchronization
233 is necessary to guarantee the integrity of the database if the
234 operating system crashes or the computer powers down unexpectedly
235 in the middle of a database update. The second time reported for SQLite is
236 when synchronization is turned off. With synchronization off,
237 SQLite is sometimes much faster, but there is a risk that an
238 operating system crash or an unexpected power failure could
239 damage the database. Generally speaking, the synchronous SQLite
240 times are for comparison against PostgreSQL (which is also
241 synchronous) and the asynchronous SQLite times are for
242 comparison against the asynchronous MySQL engine.
243 </p>
244
245 <h3>Test 1: 1000 INSERTs</h3>
246 <blockquote>
247 CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));<br>
248 INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty three');<br>
249 INSERT INTO t1 VALUES(2,75560,'seventy five thousand five hundred sixty');<br>
250 <i>... 995 lines omitted</i><br>
251 INSERT INTO t1 VALUES(998,66289,'sixty six thousand two hundred eighty nine');<br>
252 INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred twenty two');<br>
253 INSERT INTO t1 VALUES(1000,94142,'ninety four thousand one hundred forty two');<br>
254
255 </blockquote><table border=0 cellpadding=0 cellspacing=0>
256 <tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.373</td></tr>
257 <tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.114</td></tr>
258 <tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;13.061</td></tr>
259 <tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.223</td></tr>
260 </table>
261
262 <p>
263 Because it does not have a central server to coordinate access,
264 SQLite must close and reopen the database file, and thus invalidate
265 its cache, for each transaction. In this test, each SQL statement
266 is a separate transaction so the database file must be opened and closed
267 and the cache must be flushed 1000 times. In spite of this, the asynchronous
268 version of SQLite is still nearly as fast as MySQL. Notice how much slower
269 the synchronous version is, however. SQLite calls <b>fsync()</b> after
270 each synchronous transaction to make sure that all data is safely on
271 the disk surface before continuing. For most of the 13 seconds in the
272 synchronous test, SQLite was sitting idle waiting on disk I/O to complete.</p>
273
274
275 <h3>Test 2: 25000 INSERTs in a transaction</h3>
276 <blockquote>
277 BEGIN;<br>
278 CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));<br>
279 INSERT INTO t2 VALUES(1,59672,'fifty nine thousand six hundred seventy two');<br>
280 <i>... 24997 lines omitted</i><br>
281 INSERT INTO t2 VALUES(24999,89569,'eighty nine thousand five hundred sixty nine');<br>
282 INSERT INTO t2 VALUES(25000,94666,'ninety four thousand six hundred sixty six');<br>
283 COMMIT;<br>
284
285 </blockquote><table border=0 cellpadding=0 cellspacing=0>
286 <tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.900</td></tr>
287 <tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.184</td></tr>
288 <tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;0.914</td></tr>
289 <tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.757</td></tr>
290 </table>
291
292 <p>
293 When all the INSERTs are put in a transaction, SQLite no longer has to
294 close and reopen the database or invalidate its cache between each statement.
295 It also does not
296 have to do any fsync()s until the very end. When unshackled in
297 this way, SQLite is much faster than either PostgreSQL and MySQL.
298 </p>
299
300 <h3>Test 3: 25000 INSERTs into an indexed table</h3>
301 <blockquote>
302 BEGIN;<br>
303 CREATE TABLE t3(a INTEGER, b INTEGER, c VARCHAR(100));<br>
304 CREATE INDEX i3 ON t3(c);<br>
305 <i>... 24998 lines omitted</i><br>
306 INSERT INTO t3 VALUES(24999,88509,'eighty eight thousand five hundred nine');<br>
307 INSERT INTO t3 VALUES(25000,84791,'eighty four thousand seven hundred ninety one');<br>
308 COMMIT;<br>
309
310 </blockquote><table border=0 cellpadding=0 cellspacing=0>
311 <tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;8.175</td></tr>
312 <tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;3.197</td></tr>
313 <tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;1.555</td></tr>
314 <tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.402</td></tr>
315 </table>
316
317 <p>
318 There were reports that SQLite did not perform as well on an indexed table.
319 This test was recently added to disprove those rumors. It is true that
320 SQLite is not as fast at creating new index entries as the other engines
321 (see Test 6 below) but its overall speed is still better.
322 </p>
323
324 <h3>Test 4: 100 SELECTs without an index</h3>
325 <blockquote>
326 BEGIN;<br>
327 SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<1000;<br>
328 SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<1100;<br>
329 <i>... 96 lines omitted</i><br>
330 SELECT count(*), avg(b) FROM t2 WHERE b>=9800 AND b<10800;<br>
331 SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b<10900;<br>
332 COMMIT;<br>
333
334 </blockquote><table border=0 cellpadding=0 cellspacing=0>
335 <tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;3.629</td></tr>
336 <tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.760</td></tr>
337 <tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;2.494</td></tr>
338 <tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;2.526</td></tr>
339 </table>
340
341
342 <p>
343 This test does 100 queries on a 25000 entry table without an index,
344 thus requiring a full table scan. Prior versions of SQLite used to
345 be slower than PostgreSQL and MySQL on this test, but recent performance
346 enhancements have increased its speed so that it is now the fastest
347 of the group.
348 </p>
349
350 <h3>Test 5: 100 SELECTs on a string comparison</h3>
351 <blockquote>
352 BEGIN;<br>
353 SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one%';<br>
354 SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%two%';<br>
355 <i>... 96 lines omitted</i><br>
356 SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety nine%';<br>
357 SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one hundred%';<br>
358 COMMIT;<br>
359
360 </blockquote><table border=0 cellpadding=0 cellspacing=0>
361 <tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;13.409</td></tr>
362 <tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.640</td></tr>
363 <tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;3.362</td></tr>
364 <tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;3.372</td></tr>
365 </table>
366
367 <p>
368 This test still does 100 full table scans but it uses
369 uses string comparisons instead of numerical comparisons.
370 SQLite is over three times faster than PostgreSQL here and about 30%
371 faster than MySQL.
372 </p>
373
374 <h3>Test 6: Creating an index</h3>
375 <blockquote>
376 CREATE INDEX i2a ON t2(a);<br>CREATE INDEX i2b ON t2(b);
377 </blockquote><table border=0 cellpadding=0 cellspacing=0>
378 <tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.381</td></tr>
379 <tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.318</td></tr>
380 <tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;0.777</td></tr>
381 <tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.659</td></tr>
382 </table>
383
384 <p>
385 SQLite is slower at creating new indices. This is not a huge problem
386 (since new indices are not created very often) but it is something that
387 is being worked on. Hopefully, future versions of SQLite will do better
388 here.
389 </p>
390
391 <h3>Test 7: 5000 SELECTs with an index</h3>
392 <blockquote>
393 SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100;<br>
394 SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<200;<br>
395 SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<300;<br>
396 <i>... 4994 lines omitted</i><br>
397 SELECT count(*), avg(b) FROM t2 WHERE b>=499700 AND b<499800;<br>
398 SELECT count(*), avg(b) FROM t2 WHERE b>=499800 AND b<499900;<br>
399 SELECT count(*), avg(b) FROM t2 WHERE b>=499900 AND b<500000;<br>
400
401 </blockquote><table border=0 cellpadding=0 cellspacing=0>
402 <tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.614</td></tr>
403 <tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.270</td></tr>
404 <tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;1.121</td></tr>
405 <tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.162</td></tr>
406 </table>
407
408 <p>
409 All three database engines run faster when they have indices to work with.
410 But SQLite is still the fastest.
411 </p>
412
413 <h3>Test 8: 1000 UPDATEs without an index</h3>
414 <blockquote>
415 BEGIN;<br>
416 UPDATE t1 SET b=b*2 WHERE a>=0 AND a<10;<br>
417 UPDATE t1 SET b=b*2 WHERE a>=10 AND a<20;<br>
418 <i>... 996 lines omitted</i><br>
419 UPDATE t1 SET b=b*2 WHERE a>=9980 AND a<9990;<br>
420 UPDATE t1 SET b=b*2 WHERE a>=9990 AND a<10000;<br>
421 COMMIT;<br>
422
423 </blockquote><table border=0 cellpadding=0 cellspacing=0>
424 <tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.739</td></tr>
425 <tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;8.410</td></tr>
426 <tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;0.637</td></tr>
427 <tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.638</td></tr>
428 </table>
429
430 <p>
431 For this particular UPDATE test, MySQL is consistently
432 five or ten times
433 slower than PostgreSQL and SQLite. I do not know why. MySQL is
434 normally a very fast engine. Perhaps this problem has been addressed
435 in later versions of MySQL.
436 </p>
437
438 <h3>Test 9: 25000 UPDATEs with an index</h3>
439 <blockquote>
440 BEGIN;<br>
441 UPDATE t2 SET b=468026 WHERE a=1;<br>
442 UPDATE t2 SET b=121928 WHERE a=2;<br>
443 <i>... 24996 lines omitted</i><br>
444 UPDATE t2 SET b=35065 WHERE a=24999;<br>
445 UPDATE t2 SET b=347393 WHERE a=25000;<br>
446 COMMIT;<br>
447
448 </blockquote><table border=0 cellpadding=0 cellspacing=0>
449 <tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;18.797</td></tr>
450 <tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;8.134</td></tr>
451 <tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;3.520</td></tr>
452 <tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;3.104</td></tr>
453 </table>
454
455 <p>
456 As recently as version 2.7.0, SQLite ran at about the same speed as
457 MySQL on this test. But recent optimizations to SQLite have more
458 than doubled speed of UPDATEs.
459 </p>
460
461 <h3>Test 10: 25000 text UPDATEs with an index</h3>
462 <blockquote>
463 BEGIN;<br>
464 UPDATE t2 SET c='one hundred forty eight thousand three hundred eighty two' WHERE a=1;<br>
465 UPDATE t2 SET c='three hundred sixty six thousand five hundred two' WHERE a=2;<br>
466 <i>... 24996 lines omitted</i><br>
467 UPDATE t2 SET c='three hundred eighty three thousand ninety nine' WHERE a=24999;<br>
468 UPDATE t2 SET c='two hundred fifty six thousand eight hundred thirty' WHERE a=25000;<br>
469 COMMIT;<br>
470
471 </blockquote><table border=0 cellpadding=0 cellspacing=0>
472 <tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;48.133</td></tr>
473 <tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;6.982</td></tr>
474 <tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;2.408</td></tr>
475 <tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.725</td></tr>
476 </table>
477
478 <p>
479 Here again, version 2.7.0 of SQLite used to run at about the same speed
480 as MySQL. But now version 2.7.6 is over two times faster than MySQL and
481 over twenty times faster than PostgreSQL.
482 </p>
483
484 <p>
485 In fairness to PostgreSQL, it started thrashing on this test. A
486 knowledgeable administrator might be able to get PostgreSQL to run a lot
487 faster here by tweaking and tuning the server a little.
488 </p>
489
490 <h3>Test 11: INSERTs from a SELECT</h3>
491 <blockquote>
492 BEGIN;<br>INSERT INTO t1 SELECT b,a,c FROM t2;<br>INSERT INTO t2 SELECT b,a,c FROM t1;<br>COMMIT;
493 </blockquote><table border=0 cellpadding=0 cellspacing=0>
494 <tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;61.364</td></tr>
495 <tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.537</td></tr>
496 <tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;2.787</td></tr>
497 <tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.599</td></tr>
498 </table>
499
500 <p>
501 The asynchronous SQLite is just a shade slower than MySQL on this test.
502 (MySQL seems to be especially adept at INSERT...SELECT statements.)
503 The PostgreSQL engine is still thrashing - most of the 61 seconds it used
504 were spent waiting on disk I/O.
505 </p>
506
507 <h3>Test 12: DELETE without an index</h3>
508 <blockquote>
509 DELETE FROM t2 WHERE c LIKE '%fifty%';
510 </blockquote><table border=0 cellpadding=0 cellspacing=0>
511 <tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.509</td></tr>
512 <tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.975</td></tr>
513 <tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;4.004</td></tr>
514 <tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.560</td></tr>
515 </table>
516
517 <p>
518 The synchronous version of SQLite is the slowest of the group in this test,
519 but the asynchronous version is the fastest.
520 The difference is the extra time needed to execute fsync().
521 </p>
522
523 <h3>Test 13: DELETE with an index</h3>
524 <blockquote>
525 DELETE FROM t2 WHERE a>10 AND a<20000;
526 </blockquote><table border=0 cellpadding=0 cellspacing=0>
527 <tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.316</td></tr>
528 <tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;2.262</td></tr>
529 <tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;2.068</td></tr>
530 <tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.752</td></tr>
531 </table>
532
533 <p>
534 This test is significant because it is one of the few where
535 PostgreSQL is faster than MySQL. The asynchronous SQLite is,
536 however, faster then both the other two.
537 </p>
538
539 <h3>Test 14: A big INSERT after a big DELETE</h3>
540 <blockquote>
541 INSERT INTO t2 SELECT * FROM t1;
542 </blockquote><table border=0 cellpadding=0 cellspacing=0>
543 <tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;13.168</td></tr>
544 <tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.815</td></tr>
545 <tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;3.210</td></tr>
546 <tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;1.485</td></tr>
547 </table>
548
549 <p>
550 Some older versions of SQLite (prior to version 2.4.0)
551 would show decreasing performance after a
552 sequence of DELETEs followed by new INSERTs. As this test shows, the
553 problem has now been resolved.
554 </p>
555
556 <h3>Test 15: A big DELETE followed by many small INSERTs</h3>
557 <blockquote>
558 BEGIN;<br>
559 DELETE FROM t1;<br>
560 INSERT INTO t1 VALUES(1,10719,'ten thousand seven hundred nineteen');<br>
561 <i>... 11997 lines omitted</i><br>
562 INSERT INTO t1 VALUES(11999,72836,'seventy two thousand eight hundred thirty six');<br>
563 INSERT INTO t1 VALUES(12000,64231,'sixty four thousand two hundred thirty one');<br>
564 COMMIT;<br>
565
566 </blockquote><table border=0 cellpadding=0 cellspacing=0>
567 <tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;4.556</td></tr>
568 <tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;1.704</td></tr>
569 <tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;0.618</td></tr>
570 <tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.406</td></tr>
571 </table>
572
573 <p>
574 SQLite is very good at doing INSERTs within a transaction, which probably
575 explains why it is so much faster than the other databases at this test.
576 </p>
577
578 <h3>Test 16: DROP TABLE</h3>
579 <blockquote>
580 DROP TABLE t1;<br>DROP TABLE t2;<br>DROP TABLE t3;
581 </blockquote><table border=0 cellpadding=0 cellspacing=0>
582 <tr><td>PostgreSQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.135</td></tr>
583 <tr><td>MySQL:</td><td align="right">&nbsp;&nbsp;&nbsp;0.015</td></tr>
584 <tr><td>SQLite 2.7.6:</td><td align="right">&nbsp;&nbsp;&nbsp;0.939</td></tr>
585 <tr><td>SQLite 2.7.6 (nosync):</td><td align="right">&nbsp;&nbsp;&nbsp;0.254</td></tr>
586 </table>
587
588 <p>
589 SQLite is slower than the other databases when it comes to dropping tables.
590 This probably is because when SQLite drops a table, it has to go through and
591 erase the records in the database file that deal with that table. MySQL and
592 PostgreSQL, on the other hand, use separate files to represent each table
593 so they can drop a table simply by deleting a file, which is much faster.
594 </p>
595
596 <p>
597 On the other hand, dropping tables is not a very common operation
598 so if SQLite takes a little longer, that is not seen as a big problem.
599 </p>
600 <p align="center"><small><i>This page was last updated on 2026-05-29 14:19:40Z </small></i></p>
601