MySQL Forums
Forum List  »  Partitioning

Re: Large Table Architecture
Posted by: Rick James
Date: May 26, 2011 10:52AM

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.

Options: ReplyQuote

Written By
May 24, 2011 12:55PM
May 25, 2011 07:45PM
May 25, 2011 09:33PM
May 25, 2011 11:31PM
May 26, 2011 09:49AM
Re: Large Table Architecture
May 26, 2011 10:52AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.