"Does splitting a large table (20 Million rows) growing at 5 million or more
a month into smaller tables improve performance given that the table can be
split in a logical way such that 95% queries don't need to look at data
spanning across the split tables"
20 Million Rows (increasing at 5 Million rows /month)
Table Size: 650 MB (Current)
Index Size: 600 MB (Current)
I need to perform about 200 select queries per second on this table with id,
exercise and date in where clause for different queries. This table is also
joined with another table having 1M rows in it.
MySQL Master DB: Dedicated Master MySQL Database
Web Server 1: Load Balanced Web server with a slave MySQL DB
Web Server 2: Load Balanced Web server with a slave MySQL DB
All Select queries are executed on localhost by each web server. All
Insert/Update queries are executed on the Master MySQL database.