I have a table with about 50 million rows, the table itself is fairly simple
CREATE TABLE Word (id integer PRIMARY KEY, word TEXT UNIQUE)
I then do an insert
INSERT OR IGNORE INTO Word (word) VALUES (@word)
And get the Id of the value that was, (or was not), inserted.
SELECT id FROM Word WHERE word = @word
But the insert and/or the select, sometime take between 100ms and 1000ms
This is all done within a single transaction, where there are around ~1000 inserts.
So the 1000 inserts can take up to 10000ms (all within a single transaction).
Could the issue be because the table is large(ish)? While 50 million is not small, I wouldn't think it would be an issue?
How could I speed up my inserts?
