MySQL Optimize Big Table
I need to optimize a big database table. The table has billions of rows with accounting data spanning multiple years. What are some patterns to optimize performance?
I am considering splitting the table into a 'head' table with recent data, then a 'tail' table with archive data, and a MySQL view for the combined queries.
Consider tools like pt-online-schema-change https://www.percona.com/doc/percona-toolkit/3.0/pt-online-sc... or gh-ost they can rebuild the table live.. they also can add indexes without too much of performance impact. I would not use VIEW as you suggest but using partitions and partitioning data by year or something can often make sense - if most of your queres specify date range.