Database design that handles data growth with time

Question

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?

Additional Note:

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.

Answer 1

Your first tactic should be good indexing. Somewhere down the road, you'll want to consider partitioning.

You always want to think about sargable expressions.

Answer 2

If you have performance concerns, I would recommend this approach:

1. Write a script/program that creates data that will look similar to what you anticipate your real data

2. Use this program to insert enough data into your database, to the point that you think it is a realistic amount of data for a one-year period.

3. Run your queries, measure the performance, generate and analyze the query plans.

4. Create any indices that will help (closing_date_for_loan is probably a good candidate in this case, but there might be others on other tables).

5. Re-run your queries and measure performance again.

6. Repeat step 3-6 until you cant' get any more reasonable performance gains.

If you still have performance problems after this point, you might need a new approach such as archiving old data in a separate table.

Also, do your own reading on performance tuning and testing approaches for whatever RDMBS you're using (MySQL in this case).

---

If you're already experiencing performance problems and you've already tried indexing closing_date_for_loan (you said in a composite didn't work, so maybe create on index for only this column), there are other options. Your idea of moving old data to an "archive" table is not bad. The initial load might be slow but if you set it up to run regularly (daily/weekly/monthly/whatever is right for you), triggered by cron or some other scheduler then it could be a good solution. If you don't really need that old data, you could just delete it instead of archiving it. Another option might be to "soft-delete", create another column is_deleted, create an index on it, and when you query for current data, only look for records that are not "deleted" (is_deleted='N'). Of course, you'd still need the scheduled job to run at defined intervals and mark old data as "deleted".