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've got a large (many columns) MySQL database table (InnoDB) that has a fairly high number of INSERTs (~500/day). Think of the records as financial transactions. Clients need to be able to view these records and/or retrieve a report containing details about the transactions through an online system (going back as far as possible).

My question is this: Is it feasible to allow these records to accumulate and simply SELECT them from the table when preparing a listing, even accounting for significant growth in volume? The traffic would be relatively low.

We've thought of implementing a clever scheme of separating the table into years; however, that would add significantly to the complexity of the project. Assuming the table was carefully optimized and indexed adequately, would the table still be fast enough at 1,000,000+ records per year?

I hope this is not too vague or specific to warrant an answer and would like to thank those who take the time to consider responding.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

With no growth, you're looking at 1.8 million rows in 10 years. With 100% growth per year, 93 million rows in 10 years. I'd estimate your actual upper bound to be 66 million rows after 10 years.

It's practical to keep that many rows in a transactional table if you partition the table. (You don't have to do that for a while.)

We've thought of implementing a clever scheme of separating the table into years

Millions of people have thought of this. Don't do it.

Use a single, unpartitioned table until you have a performance problem. Then partition the table. (You have a lot of time between now and then to test and practice.) Finally, after you tune the server for the new workload, consider upgrading hardware. (Banks don't run on shared web servers that cost $2.95 a month.)

Also consider switching now to PostgreSQL. It has a better query optimizer than MySQL; it has more indexing options, including function-based indexes and partial indexes; and in my experience seems to handle 100-million-row tables better than MySQL.

share|improve this answer
    
Also you can very easily simulate the insertion of 1M, 10M, or even 100M rows and then test the performance of the rest of your application. With proper application design and the right indexes nothing should really change as indexed operations using BTREE indexes should still be O(log(N)). Any non-constant or non-logarithmic behavior will immediately be apparent. –  sehrope Jul 23 '13 at 1:58
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.