The index is the least of the issue with the SQLite implementation. It's calling one INSERT per each record in that version, so the benchmark is spending something like 99.8% of its time opening and closing transactions as it sets up the database.
Fixing that on my machine took the sqlite3_file_open benchmark from 16.910 seconds to 1.033 seconds. Adding the index brought it down to 0.040 seconds.
Also, I've never really dug into what's going on, but the dbm implementation is pretty slow on Windows, at least when I've tried to use it.
I was working on a project to insert a billion rows in SQLite under a minute, batching the inserts made it crazy fast compared to individual transactions.
It uses a text file to store keys and pointers into a binary file for values. It works .. most of the time .. but yeah, that's not going to win any speed awards.
Sure, mutating data sets might be a useful use case. But, inserting thousands of items at once one at a time in a tight loop, then asking for all of them is testing an unusual use case in my opinion.
My point was that we're comparing apples and oranges. By default, I think, Python's dbm implementation doesn't do any sort of transaction or even sync after every insert, where as SQLite does have a pretty hefty atomic guarantee after each INSERT, so they're quite different actions.
Fixing that on my machine took the sqlite3_file_open benchmark from 16.910 seconds to 1.033 seconds. Adding the index brought it down to 0.040 seconds.
Also, I've never really dug into what's going on, but the dbm implementation is pretty slow on Windows, at least when I've tried to use it.