Findings
Only comparisons that repeated across three independent runs.
one transaction (500 rows) is ~250.00× faster than commit per row250.00× · CV 0.0%correlated EXISTS is ~128.69× slower than IN (subquery)128.69× · CV 2.2%GROUP BY score (indexed) is ~41.67× faster than GROUP BY payload (no index)41.67× · CV 2.0%ORDER BY score (indexed) is ~29.41× faster than ORDER BY payload (no index)29.41× · CV 3.7%primary key lookup (id = ?) is ~26.32× faster than search on an unindexed column (0 matches)26.32× · CV 2.1%WHERE score = (indexed) is ~25.00× faster than WHERE score = (no index)25.00× · CV 0.0%COUNT(*) is ~1.81× faster than COUNT(score)1.81× · CV 2.1%SELECT * is ~1.68× slower than SELECT id, score1.68× · CV 4.1%WAL (journal_mode=WAL) is ~1.26× slower than DELETE (default)1.26× · CV 8.2%executemany (one transaction) is ~1.25× faster than execute() per row1.25× · CV 2.0%dict merge in Python is ~1.19× faster than JOIN in SQL1.19× · CV 1.2%parameter binding (?) is ~1.17× faster than string interpolation1.17× · CV 3.2%JOIN is ~1.16× slower than IN (subquery)1.16× · CV 9.3%LIKE 'item-0123%' (prefix) is ~1.11× faster than LIKE '%12345%' (contains)1.11× · CV 3.4%sqlite3.Row is ~1.09× slower than tuple (default)1.09× · CV 2.4%