Plan for managing financial transactions in an easily and retrievable manner, indefinitely I've got a large (many columns) MySQL database table (InnoDB) that has a fairly high number of INSERTs (~500/day). Think of the rows as financial transactions. Clients need to be able to view these rows 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 rows 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+ rows 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.
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.