Manage the amount of data on tables

Question

I can have several tables with 10+ millions rows in by year.

I would to know what's the best approach to maintain responsiveness with this amount of datas and I have two ideas.

• Don't touch these tables in the time, hoping that with the design of the database it will still be responsive despite data quantity

• Copy the structure of these tables in archive tables and move old datas in, in order to keep "production tables" reduced

Or do you have other ideas?

I can't delete old datas because we have to make statistics over several years.

Answer 1

• 10M rows/year is less than 1/sec. 100 simple queries per second is not a problem
• What do the SELECTs look like? If they are getting slower and slower, then they are probably doing "table scans" and would probably benefit greatly from indexing. (No, don't blindly "index every column". Let's see the queries.)
• Please provide SHOW CREATE TABLE for advice on dataypes. (Example: don't blindly use an 8-byte BIGINT when a 1-byte TINYINT will suffice.)
• Will you be purging "old" data after some number of months? If so plan now by using Partitioning
• If some selects are taking sums/averages/etc over many months' worth of data, use Summary Tables
• You probably you can "delete old data" -- Keep the summary tables, but purge the raw data. The summary data is both faster and smaller. Win, win.
• If the table holds "sensor" (weather, vehicle, etc) data, see https://mysql.rjweb.org/doc.php/mysql_sensor for more tips.
• innodb_buffer_pool_size should be about 70% of available RAM.
• (I say "no" to both of your bullet items!)