MySQL Forums
Forum List  »  Partitioning

Re: Large Table Architecture
Posted by: Darren Mitchell
Date: May 26, 2011 09:49AM

As of right now, that reporting query is the only one. They haven't decided yet on how else to use the data. They will probably use to build other reports that have yet to be determined.

I did change the key buffer to 20GB per your suggestion.

How do you do the INSERTs? Batched? LOAD DATA? Via another table? Does the data come in all at once, or trickle in throughout the day?

* It is currently loaded every morning via LOAD DATA script in our ETL tool/grabs a file every morning. Normalizing should be pretty easy to setup with look up tables for the static content (ACTIONS)

CREATE TABLE `RESP_CA_MasterSent_List` (
  `Email` varchar(125) COLLATE latin1_general_ci DEFAULT NULL,
  `Campaign` varchar(75) COLLATE latin1_general_ci DEFAULT NULL,
  `Action` varchar(15) COLLATE latin1_general_ci DEFAULT NULL,
  `Description` varchar(75) COLLATE latin1_general_ci DEFAULT NULL,
  `Timestamp` datetime DEFAULT NULL,
  `LaunchID` int(10) DEFAULT NULL,
  `OPID` varchar(15) COLLATE latin1_general_ci DEFAULT NULL,
  `InFile` varchar(25) COLLATE latin1_general_ci DEFAULT NULL,
  KEY `IDX_MasterSent_CampAction` (`Campaign`,`Action`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

This is currently the table the query mentioned runs against, I will be breaking this down to a normalized table as well.

The OPID values vary quite a bit, but to repeat. For instance
The three values for the ACTION column are:

So one person (OPID), will exist in 1 (SENT) but could also have 1 or both of the other values per campaign.

I have one table loaded with all the data (table mentioned above), this allows me to experiment with various techniques in parallel until I get it right. From start to finish I can run the load process in about 8 hrs, probably faster if I switch to bulk loading.

I like your idea of utilizing the timestamp field as an index so next, I will implement the normalization and summary table process as described.

Options: ReplyQuote

Written By
May 24, 2011 12:55PM
May 25, 2011 07:45PM
May 25, 2011 09:33PM
May 25, 2011 11:31PM
Re: Large Table Architecture
May 26, 2011 09:49AM
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.