I have a big table that stores video rental data with the following columns:
id, title, language, duration, owner, remarks, closing_date_for_loan
Assuming every day there are thousands of data inserts to this table, then within a year, I could have a million rows of data. The search on the data record involves range query on several columns and will always contain a
WHERE closing_date_for_loan > NOW() condition.
To maintain this table, I could perform a query to transfer data with
closing_date_for_loan < NOW() out of this big table periodically so that the table won't get too big causing excessive query times. I am looking for a more automated way of handling such data growth similar to how a log rotation works. Any ideas?
I have tested a few composite index and the query time can range from a few seconds to 50s if the row gets to 5 million. Range queries can be hard to optimize, so I am looking for other ways like keeping the table to a manageable size.