MySQL Forums
Forum List  »  Performance

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

Options: ReplyQuote


Subject
Views
Written By
Posted
JOIN vs Denormalize table vs Vertical partition
4780
November 12, 2009 03:26AM


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.