Test 8: 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;

SQLite 3.3.3 (sync):	1.872
SQLite 3.3.3 (nosync):	1.853
SQLite 2.8.17 (sync):	2.444
SQLite 2.8.17 (nosync):	2.478
PostgreSQL 8.1.2:	199.823
MySQL 5.0.18 (sync):	3.763
MySQL 5.0.18 (nosync):	3.725
FirebirdSQL 1.5.2:	5.187

* Performance of PostgreSQL in this test is most probably heavily impacted by psql command line utility. Same test when run form pgAdmin III GUI completed in 5 seconds.

Test 9: 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;

SQLite 3.3.3 (sync):	0.562
SQLite 3.3.3 (nosync):	0.573
SQLite 2.8.17 (sync):	0.543
SQLite 2.8.17 (nosync):	0.532
PostgreSQL 8.1.2:	1.663
MySQL 5.0.18 (sync):	1.930
MySQL 5.0.18 (nosync):	4.656
FirebirdSQL 1.5.2:	1.804

Test 10: 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;

SQLite 3.3.3 (sync):	1.883
SQLite 3.3.3 (nosync):	1.894
SQLite 2.8.17 (sync):	1.994
SQLite 2.8.17 (nosync):	1.973
PostgreSQL 8.1.2:	23.933
MySQL 5.0.18 (sync):	16.348
MySQL 5.0.18 (nosync):	17.383
FirebirdSQL 1.5.2:	15.542

Test 11: 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;

SQLite 3.3.3 (sync):	1.386
SQLite 3.3.3 (nosync):	1.365
SQLite 2.8.17 (sync):	1.168
SQLite 2.8.17 (nosync):	1.121
PostgreSQL 8.1.2:	24.672
MySQL 5.0.18 (sync):	16.469
MySQL 5.0.18 (nosync):	15.491
FirebirdSQL 1.5.2:	21.583

Test 12: INSERTs from a SELECT

BEGIN;
INSERT INTO t1 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t1;
COMMIT;

SQLite 3.3.3 (sync):	1.179
SQLite 3.3.3 (nosync):	1.116
SQLite 2.8.17 (sync):	1.864
SQLite 2.8.17 (nosync):	1.526
PostgreSQL 8.1.2:	1.091
MySQL 5.0.18 (sync):	0.986
MySQL 5.0.18 (nosync):	0.933
FirebirdSQL 1.5.2:	4.782

Test 13: INNER JOIN with index on one side

SELECT t1.a FROM t1 INNER JOIN t2 ON t1.b=t2.b;

SQLite 3.3.3 (sync):	0.371
SQLite 3.3.3 (nosync):	0.369
SQLite 2.8.17 (sync):	0.273
SQLite 2.8.17 (nosync):	0.275
PostgreSQL 8.1.2:	5.981
MySQL 5.0.18 (sync):	0.408
MySQL 5.0.18 (nosync):	0.603
FirebirdSQL 1.5.2:	1.099

Test 14: INNER JOIN on text field with index on one side

SELECT t1.a FROM t1 INNER JOIN t3 ON t1.c=t3.c;

SQLite 3.3.3 (sync):	0.383
SQLite 3.3.3 (nosync):	0.376
SQLite 2.8.17 (sync):	0.309
SQLite 2.8.17 (nosync):	0.291
PostgreSQL 8.1.2:	1.324
MySQL 5.0.18 (sync):	0.404
MySQL 5.0.18 (nosync):	0.558
FirebirdSQL 1.5.2:	0.454

Test 15: 100 SELECTs with subqueries. Subquery is using an index

SELECT t1.a FROM t1 WHERE t1.b IN (SELECT t2.b FROM t2 WHERE t2.b>=0 AND t2.b<1000);
SELECT t1.a FROM t1 WHERE t1.b IN (SELECT t2.b FROM t2 WHERE t2.b>=100 AND t2.b<1100);
SELECT t1.a FROM t1 WHERE t1.b IN (SELECT t2.b FROM t2 WHERE t2.b>=200 AND t2.b<1200);
... 94 lines omitted
SELECT t1.a FROM t1 WHERE t1.b IN (SELECT t2.b FROM t2 WHERE t2.b>=9700 AND t2.b<10700);
SELECT t1.a FROM t1 WHERE t1.b IN (SELECT t2.b FROM t2 WHERE t2.b>=9800 AND t2.b<10800);
SELECT t1.a FROM t1 WHERE t1.b IN (SELECT t2.b FROM t2 WHERE t2.b>=9900 AND t2.b<10900);

SQLite 3.3.3 (sync):	7.877
SQLite 3.3.3 (nosync):	8.040
SQLite 2.8.17 (sync):	4.387
SQLite 2.8.17 (nosync):	4.381
PostgreSQL 8.1.2:	6.245
MySQL 5.0.18 (sync):	16.891
MySQL 5.0.18 (nosync):	38.447
FirebirdSQL 1.5.2:	37.439

Test 16: DELETE without an index

DELETE FROM t2 WHERE c LIKE '%fifty%';

SQLite 3.3.3 (sync):	0.528
SQLite 3.3.3 (nosync):	0.429
SQLite 2.8.17 (sync):	1.228
SQLite 2.8.17 (nosync):	0.984
PostgreSQL 8.1.2:	0.336
MySQL 5.0.18 (sync):	0.394
MySQL 5.0.18 (nosync):	0.532
FirebirdSQL 1.5.2:	0.404

Test 17: DELETE with an index

DELETE FROM t2 WHERE a>10 AND a<20000;

SQLite 3.3.3 (sync):	0.866
SQLite 3.3.3 (nosync):	0.627
SQLite 2.8.17 (sync):	1.275
SQLite 2.8.17 (nosync):	0.817
PostgreSQL 8.1.2:	0.283
MySQL 5.0.18 (sync):	0.541
MySQL 5.0.18 (nosync):	1.336
FirebirdSQL 1.5.2:	5.033

Test 18: A big INSERT after a big DELETE

INSERT INTO t2 SELECT * FROM t1;

SQLite 3.3.3 (sync):	0.973
SQLite 3.3.3 (nosync):	0.865
SQLite 2.8.17 (sync):	1.680
SQLite 2.8.17 (nosync):	1.336
PostgreSQL 8.1.2:	0.727
MySQL 5.0.18 (sync):	0.762
MySQL 5.0.18 (nosync):	1.088
FirebirdSQL 1.5.2:	4.171

Test 19: 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;

SQLite 3.3.3 (sync):	0.155
SQLite 3.3.3 (nosync):	0.133
SQLite 2.8.17 (sync):	0.160
SQLite 2.8.17 (nosync):	0.255
PostgreSQL 8.1.2:	2.635
MySQL 5.0.18 (sync):	1.402
MySQL 5.0.18 (nosync):	1.133
FirebirdSQL 1.5.2:	0.667

Test 20: DROP TABLE

DROP TABLE t1;
DROP TABLE t2;

SQLite 3.3.3 (sync):	0.138
SQLite 3.3.3 (nosync):	0.392
SQLite 2.8.17 (sync):	0.188
SQLite 2.8.17 (nosync):	0.257
PostgreSQL 8.1.2:	0.229
MySQL 5.0.18 (sync):	0.125
MySQL 5.0.18 (nosync):	0.058
FirebirdSQL 1.5.2:	0.133