Test Environment
- RedHat8.0 on VMWare (Windows2000SP4)
- PentiumIII 733Mhz
- MySQL4.017
- SQLite2.8.11
Test 1: 1000 INSERTs
CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));
INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty
three');
INSERT INTO t1 VALUES(2,75560,'seventy five thousand five hundred
sixty');
... 995 lines omitted
INSERT INTO t1 VALUES(998,66289,'sixty six thousand two hundred eighty
nine');
INSERT INTO t1 VALUES(999,24322,'twenty four thousand three hundred
twenty two');
INSERT INTO t1 VALUES(1000,94142,'ninety four thousand one hundred
forty two');
MySQL: |
0.583 |
SQLite 2.8.11: |
9.635 |
SQLite 2.8.11 (nosync): |
0.865 |
Test 2: 25000 INSERTs in a transaction
BEGIN;
CREATE TABLE t2(a INTEGER, b INTEGER, c VARCHAR(100));
INSERT INTO t2 VALUES(1,298361,'two hundred ninety eight thousand three
hundred sixty one');
... 24997 lines omitted
INSERT INTO t2 VALUES(24999,447847,'four hundred forty seven thousand
eight hundred forty seven');
INSERT INTO t2 VALUES(25000,473330,'four hundred seventy three thousand
three hundred thirty');
COMMIT;
MySQL: |
10.822 |
SQLite 2.8.11: |
2.334 |
SQLite 2.8.11 (nosync): |
2.148 |
Test 3: 100 SELECTs without an index
SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<1000;
SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<1100;
SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<1200;
... 94 lines omitted
SELECT count(*), avg(b) FROM t2 WHERE b>=9700 AND b<10700;
SELECT count(*), avg(b) FROM t2 WHERE b>=9800 AND b<10800;
SELECT count(*), avg(b) FROM t2 WHERE b>=9900 AND b<10900;
MySQL: |
9.617 |
SQLite 2.8.11: |
16.191 |
SQLite 2.8.11 (nosync): |
16.288 |
Test 4: 100 SELECTs on a string comparison
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one%';
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%two%';
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%three%';
... 94 lines omitted
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety eight%';
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%ninety nine%';
SELECT count(*), avg(b) FROM t2 WHERE c LIKE '%one hundred%';
MySQL: |
11.320 |
SQLite 2.8.11: |
18.787 |
SQLite 2.8.11 (nosync): |
18.515 |
Test 5: Creating an index
CREATE INDEX i2a ON t2(a);
CREATE INDEX i2b ON t2(b);
MySQL: |
1.305 |
SQLite 2.8.11: |
2.210 |
SQLite 2.8.11 (nosync): |
2.146 |
Test 6: 5000 SELECTs with an index
SELECT count(*), avg(b) FROM t2 WHERE b>=0 AND b<100;
SELECT count(*), avg(b) FROM t2 WHERE b>=100 AND b<200;
SELECT count(*), avg(b) FROM t2 WHERE b>=200 AND b<300;
... 4994 lines omitted
SELECT count(*), avg(b) FROM t2 WHERE b>=499700 AND b<499800;
SELECT count(*), avg(b) FROM t2 WHERE b>=499800 AND b<499900;
SELECT count(*), avg(b) FROM t2 WHERE b>=499900 AND b<500000;
MySQL: |
6.477 |
SQLite 2.8.11: |
4.575 |
SQLite 2.8.11 (nosync): |
4.622 |
Test 7: 1000 UPDATEs without an index
BEGIN;
UPDATE t1 SET b=b*2 WHERE a>=0 AND a<10;
UPDATE t1 SET b=b*2 WHERE a>=10 AND a<20;
... 996 lines omitted
UPDATE t1 SET b=b*2 WHERE a>=9980 AND a<9990;
UPDATE t1 SET b=b*2 WHERE a>=9990 AND a<10000;
COMMIT;
MySQL: |
13.105 |
SQLite 2.8.11: |
1.809 |
SQLite 2.8.11 (nosync): |
1.699 |
Test 8: 25000 UPDATEs with an index
BEGIN;
UPDATE t2 SET b=271822 WHERE a=1;
UPDATE t2 SET b=28304 WHERE a=2;
... 24996 lines omitted
UPDATE t2 SET b=442549 WHERE a=24999;
UPDATE t2 SET b=423958 WHERE a=25000;
COMMIT;
MySQL: |
35.726 |
SQLite 2.8.11: |
8.501 |
SQLite 2.8.11 (nosync): |
8.221 |
Test 9: 25000 text UPDATEs with an index
BEGIN;
UPDATE t2 SET c='four hundred sixty eight thousand twenty six' WHERE
a=1;
UPDATE t2 SET c='one hundred twenty one thousand nine hundred twenty
eight' WHERE a=2;
... 24996 lines omitted
UPDATE t2 SET c='thirty five thousand sixty five' WHERE a=24999;
UPDATE t2 SET c='three hundred forty seven thousand three hundred
ninety three' WHERE a=25000;
COMMIT;
MySQL: |
30.211 |
SQLite 2.8.11: |
4.639 |
SQLite 2.8.11 (nosync): |
4.477 |
Test 10: INSERTs from a SELECT
BEGIN;
INSERT INTO t1 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t1;
COMMIT;
MySQL: |
4.515 |
SQLite 2.8.11: |
7.726 |
SQLite 2.8.11 (nosync): |
5.502 |
Test 11: DELETE without an index
DELETE FROM t2 WHERE c LIKE '%fifty%';
MySQL: |
2.078 |
SQLite 2.8.11: |
11.830 |
SQLite 2.8.11 (nosync): |
2.220 |
Test 12: DELETE with an index
DELETE FROM t2 WHERE a>10 AND a<20000;
MySQL: |
5.459 |
SQLite 2.8.11: |
4.405 |
SQLite 2.8.11 (nosync): |
2.222 |
Test 13: A big INSERT after a big DELETE
INSERT INTO t2 SELECT * FROM t1;
MySQL: |
4.949 |
SQLite 2.8.11: |
9.748 |
SQLite 2.8.11 (nosync): |
5.515 |
Test 14: A big DELETE followed by many small INSERTs
BEGIN;
DELETE FROM t1;
INSERT INTO t1 VALUES(1,29676,'twenty nine thousand six hundred seventy
six');
... 2997 lines omitted
INSERT INTO t1 VALUES(2999,37835,'thirty seven thousand eight hundred
thirty five');
INSERT INTO t1 VALUES(3000,97817,'ninety seven thousand eight hundred
seventeen');
COMMIT;
MySQL: |
1.500 |
SQLite 2.8.11: |
0.529 |
SQLite 2.8.11 (nosync): |
0.459 |
Test 15: DROP TABLE
DROP TABLE t1;
DROP TABLE t2;
MySQL: |
0.060 |
SQLite 2.8.11: |
1.586 |
SQLite 2.8.11 (nosync): |
0.421 |