Skip navigation links

MySQL Forums :: Performance :: Will fixed row format increase performance


Advanced Search

Will fixed row format increase performance
Posted by: Denis Chernyaev ()
Date: October 04, 2012 02:24AM

Hello.
I've got a table containing about 15 million rows, its size is over 3Gb, CREATE TABLE is the following:

CREATE TABLE `t` (
`f1` int(11) unsigned NOT NULL AUTO_INCREMENT,
`f2` varchar(25) NOT NULL DEFAULT '',
`f3` mediumint(5) unsigned DEFAULT NULL,
`f4` mediumint(9) unsigned DEFAULT NULL,
`f5` varchar(50) DEFAULT NULL,
`f6` varchar(70) DEFAULT NULL,
`f7` tinyint(1) NOT NULL DEFAULT '0',
`f8` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`f9` varchar(16) NOT NULL DEFAULT '',
`f10` tinyint(1) unsigned NOT NULL DEFAULT '0',
`f11` datetime DEFAULT NULL,
`f12` tinyint(1) unsigned NOT NULL DEFAULT '0' ,
`f13` tinyint(1) unsigned NOT NULL DEFAULT '1',
`f14` tinyint(1) unsigned NOT NULL DEFAULT '1',
`f15` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`f16` varchar(16) DEFAULT '0',
`f17` datetime DEFAULT '0000-00-00 00:00:00',
`f18` enum('Y','N') DEFAULT 'N',
`f19` enum('Y','N') DEFAULT 'N',
`f20` varchar(75) DEFAULT NULL,
`f21` text,
`f22` tinyint(1) NOT NULL DEFAULT '0',
`f23` mediumint(9) DEFAULT NULL,
`f24` int(1) DEFAULT '0',
`f25` varchar(3) DEFAULT NULL,
PRIMARY KEY (`f1`,`f12`),
KEY `IDX_1` (`f8`),
KEY `IDX_2` (`f11`),
KEY `IDX_3` (`f2`,`f4`,`f8`),
KEY `IDX_4` (`f2`,`f3`,`f8`),
KEY `IDX_5` (`f4`,`f8`),
KEY `IDX_6` (`f17`),
KEY `IDX_7` (`f14`),
KEY `IDX_8` (`f25`),
KEY `IDX_9` (`f2`),
KEY `IDX_10` (`f9`),
KEY `IDX_11` (`f3`,`f8`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=cp1251 ROW_FORMAT=DYNAMIC
/*!50100 PARTITION BY LIST (f12)
(PARTITION p0 VALUES IN (0) ENGINE = MyISAM,
PARTITION p1 VALUES IN (1,3) ENGINE = MyISAM,
PARTITION p2 VALUES IN (2) ENGINE = MyISAM) */

There's always high insert/delete/select activity. I've got an idea to convert this table into the FIXED ROW format. I know it will inflate it greatly and make the table easier to repair in case of crashing, but I would like to know whether it will increase query performance as well, or will it create any difficulties related to table size?

Options: ReplyQuote


Subject Views Written By Posted
Will fixed row format increase performance 1661 Denis Chernyaev 10/04/2012 02:24AM
Re: Will fixed row format increase performance 738 Rick James 10/04/2012 09:57PM
Re: Will fixed row format increase performance 636 Denis Chernyaev 10/04/2012 11:06PM


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.