JOIN vs Denormalize table vs Vertical partition
Posted by:
Laavanya K
Date: November 12, 2009 03:26AM
Hi,
I have some questions about JOIN and having DE NORMALIZE tables.
I have an option of having denormalize table or having to perform JOIN heavily.
I don't have a particular query used, as the API should allow almost any data to be pulled out from the database.
In my experience, having a denormalize table seems to be faster as the tables grows bigger.
With de-normalize, i have about 30-50 columns.
I read about vertical partitions. How does that work now as I know the 5.1 doesn't support automated vertical partitions. Does this mean, I would need to do joins here too?
Would it be slower if I use JOIN for 2 tables, or having all the columns in just one table?
What about having a de-normalize table like below vs a normalize table.
Here is one example of the table :
CREATE TABLE `test_info` (
`sub_test_id` int(10) unsigned NOT NULL,
`sub_test` varchar(50) NOT NULL,
`proximity` float(4,2) unsigned NOT NULL DEFAULT '0.00',
`test_id` int(10) unsigned NOT NULL,
`test` text NOT NULL,
`test_hash` varchar(50) CHARACTER SET ascii NOT NULL,
`test_relevancy` mediumint(8) unsigned NOT NULL DEFAULT '0',
`mention_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`datetime` datetime NOT NULL,
`type` set('News','Article','Blog','Multimedia','Microblog','Discussion','Other') NOT NULL,
`source` set('Technorati','Digg','YouTube','Flickr','Yahoo News','Twitter','Google Blog Search','Google Group Search','Custom Source') NOT NULL,
`continent` enum('Unknown','Asia','Europe','Africa','Australasia','North America','Antarctic','South America','America') NOT NULL,
`country` enum('UNKNOWN','AF','AL','DZ','AS','AD','AO','AI','AQ','AG','AR','AM','AW','AU','AT','AZ','BS','BH','BD','BB','BY','BE','BZ','BJ','BM','BT','BO','BA','BW','BV','BR','IO','BN','BG','BF','BI','KH','CM','CA','CV','KY','CF','TD','CL','CN','CX','CC','CO','KM','CG','CD','CK','CR','CI','HR','CU','CY','CZ','DK','DJ','DM','DO','EC','EG','SV','GQ','ER','EE','ET','FK','FO','FJ','FI','FR','GF','PF','TF','GA','GM','GE','DE','GH','GI','GR','GL','GD','GP','GU','GT','GN','GW','GY','HT','HM','VA','HN','HK','HU','IS','IN','ID','IR','IQ','IE','IL','IT','JM','JP','JO','KZ','KE','KI','KP','KR','KW','KG','LA','LV','LB','LS','LR','LY','LI','LT','LU','MO','MK','MG','MW','MY','MV','ML','MT','MH','MQ','MR','MU','YT','MX','FM','MD','MC','MN','ME','MS','MA','MZ','MM','NA','NR','NP','NL','AN','NC','NZ','NI','NE','NG','NU','NF','MP','NO','OM','PK','PW','PS','PA','PG','PY','PE','PH','PN','PL','PT','PR','QA','RE','RO','RU','RW','SH','KN','LC','PM','VC','WS','SM','ST','SA','SN','CS','SC','SL','SG','SK','SI','SB','SO','ZA','GS','ES','LK','SD','SR','SJ','SZ','SE','CH','SY','TW','TJ','TZ','TH','TL','TG','TK','TO','TT','TN','TR','TM','TC','TV','UG','UA','AE','GB','UK','US','UM','UY','UZ','VU','VE','VN','VG','VI','WF','EH','YE','ZM','ZW') NOT NULL,
`url` varchar(1000) CHARACTER SET ascii NOT NULL,
`title` text NOT NULL,
`description` text NOT NULL,
`reach` float(15,5) unsigned NOT NULL DEFAULT '0.00000',
`relevancy` int(10) unsigned NOT NULL,
`domain` varchar(60) NOT NULL,
`domain_id` int(10) unsigned NOT NULL,
username varchar(60) NOT NULL,
user_id int(10) unsigned NOT NULL,
PRIMARY KEY (`sub_test_id`,`mention_id`,`test_id`),
KEY `date` (`datetime`),
KEY `test_id` (`test_id`),
KEY `sub_test_id` (`sub_test_id`),
KEY `mention_id` (`mention_id`),
KEY `continent` (`continent`),
KEY `country` (`country`),
KEY `source` (`source`),
KEY `test_hash` (`test_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=2938 DEFAULT CHARSET=utf8