"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"
Table Description:
20 Million Rows (increasing at 5 Million rows /month)
Table Size: 650 MB (Current)
Index Size: 600 MB (Current)
Field
Type
Null
Key
Default
Extra
id
bigint(20) unsigned
MUL
0
exercise
varchar(10)
MUL
score
tinyint(4)
0
date
Datetime
YES
MUL
NULL
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.
System Description:
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.
