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.

  • What RDBMS? MySQL?
    – Philᵀᴹ
    Mar 15, 2013 at 14:37
  • @Phil, it's updated. Mar 15, 2013 at 14:39
  • 1
    Do you have an index on closing_date_for_loan? You could write a script that creates millions of records that look similar to your real data, and then do a performance test on your queries to see how bad the problem really is. Mar 15, 2013 at 14:44
  • @FrustratedWithFormsDesigner, no, I don't. I have indexes on some other columns already. Mar 15, 2013 at 14:47
  • @FrustratedWithFormsDesigner, I 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 reducing the table size. Mar 15, 2013 at 14:55

2 Answers 2


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.


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".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.