Re: Large Table Architecture

[Rick James]

Given that you have one big insert per day, here is something else to ponder...

Each day:
1. Load the new data into a separate table (not the main one). Lets call it `Today`. `Today` has no indexes (yet); this will make a very fast load (either via LOAD DATA or via "batched" INSERTs).
2. ALTER TABLE Today ADD INDEX ... -- if any are needed. (None needed so far.)
3. Add rows to the Summary table(s) (via INSERT...SELECT... FROM `Today` GROUP BY...).
4. Archive Today by doing INSERT INTO RESP_CA_MasterSent_List SELECT * FROM `Today`;

With that technique, no indexes are needed on RESP_CA_MasterSent_List. However, future requirements may necessitate some index. Generally for a "Fact" table (which this seems to be), I recommend only a INT UNSIGNED AUTO_INCREMENT (or BIGINT, if you might get more than 4 billion rows). This will facilitate efficient crawls of that huge table in the future. Also, an AI index is very cheap (unlike your current index's performance problem).

Keep in mind that a table of that size will take hour(s) to scan / ALTER / etc. Having _no_ index would mean that _any_ operation would be a table scan.

You really need to think ahead to what else you might want to pull from the Fact table; as the table grows, efficiency fixes will become more time consuming.

A single MyISAM query will use only a single core. (InnoDB makes some use of multiple cores in a single query, but not much.)

So far, the 99GB and 24 cores are overkill (once you have made the changes I suggest). Future requirements may lead to the RAM/cores being useful.