Greetings,
I am using the latest release of SQLITE3 as of this writing ( Version 3.34.0 ).
After inserting 60,000 records into the following table (for test purposes):
CREATE TABLE PlayerConnections
(
TargetPlayerId INTEGER NOT NULL,
SourcePlayerId INTEGER NOT NULL
);
When attempting to execute the following same exact query 60,000 times in a simple for() loop:
"SELECT 1 FROM PlayerConnections WHERE (TargetPlayerId = 20000 AND SourcePlayerId = 1)"
Executing this loop takes 60-80 seconds to perform such a simple query
The interesting thing is that the first queries are executed quickly, as expected, 500-1000 queries take <1ms.
By the time the loop reaches the 50000th iteration, approximately 1 in 3 queries cost 15ms to execute, sometimes 30ms, and sometimes 45ms resulting in a serious performance decrease over-all.