Database growth is a problem over time #324
Description
So glime has been chugging along great for the last two and a half years, generally without any problem. Recently though, performance has become unacceptable. Through a bit of digging, I found out that the triviagameslog (the database that keeps track of the round number + the question asked) has been growing nonstop since the beginning. It reached more that 1,250,000 entries and that has been pulling performance to a crawl when grabbing a new question. Sometimes taking more than 20 seconds to grab a new question on top of the waiting period on a high powered machine.
I went ahead and confirmed this was the problem by only dropping the triviagameslog database and starting the bot back up on the same machine. Wait times are now in the 2 seconds range.
For reference, glime's database is 163MB. After dropping triviagameslog it is only about 15MB.
Do we have a plan to address this? Chat suggested that the use of SQLite might be contributing to the problem but I think reusing round numbers in the database would be an easier solution instead switching to a new DB just so we can add a new entry for every question asked since round number do repeat after a while