Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
What RDBMS? MySQL? –  FreshPhilOfSO Mar 15 '13 at 14:37
    
@Phil, it's updated. –  Question Overflow Mar 15 '13 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. –  FrustratedWithFormsDesigner Mar 15 '13 at 14:44
    
@FrustratedWithFormsDesigner, no, I don't. I have indexes on some other columns already. –  Question Overflow Mar 15 '13 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. –  Question Overflow Mar 15 '13 at 14:55
show 3 more comments

2 Answers

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.

share|improve this answer
    
+1 for sargable expressions! –  Joel Brown Mar 16 '13 at 0:47
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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