After one very quick look, the index on folderid alone is unnecessary since you have another index in which that field is the high-order field.
On Mon, Dec 14, 2009 at 12:31 PM, Sudhir N <sudhir_nima...@yahoo.com> wrote: > I have following table structure, I have to use merge storage engine. > Please have a look, and provide feedback if theres some thing wrong or if > there's space for optimization. > > > /*Table structure for table `messages2009` */ > > CREATE TABLE `messages2009` ( > `id` varchar(36) NOT NULL default '', > `folderid` varchar(36) NOT NULL default '', > `fromid` int(11) NOT NULL default '0', > `fromtype` varchar(10) NOT NULL default '', > `toid` int(11) NOT NULL default '0', > `totype` varchar(10) NOT NULL default '', > `subject` varchar(255) default NULL, > `body` text, > `readbyrecipient` tinyint(1) NOT NULL default '0', > `deletedbyauthor` tinyint(1) NOT NULL default '0', > `deletedbyrecipient` tinyint(1) NOT NULL default '0', > `threadid` varchar(36) NOT NULL default '', > `senttime` timestamp NOT NULL default CURRENT_TIMESTAMP, > PRIMARY KEY (`id`), > KEY `folderid` (`folderid`), > KEY `threadid` (`threadid`), > KEY `inboxfolderindex` (`folderid`,`toid`,`totype`), > KEY `sentitemsindex` (`fromid`,`fromtype`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1; > > /*Table structure for table `messages` */ > /*Merge table definition that covers all message tables*/ > > CREATE TABLE `messages` ( > `id` varchar(36) NOT NULL default '', > `folderid` varchar(36) NOT NULL default '', > `fromid` int(11) NOT NULL default '0', > `fromtype` varchar(10) NOT NULL default '', > `toid` int(11) NOT NULL default '0', > `totype` varchar(10) NOT NULL default '', > `subject` varchar(255) default NULL, > `body` text, > `readbyrecipient` tinyint(1) NOT NULL default '0', > `deletedbyauthor` tinyint(1) NOT NULL default '0', > `deletedbyrecipient` tinyint(1) NOT NULL default '0', > `threadid` varchar(36) NOT NULL default '', > `senttime` timestamp NOT NULL default CURRENT_TIMESTAMP, > PRIMARY KEY (`id`), > KEY `folderid` (`folderid`), > KEY `threadid` (`threadid`), > KEY `inboxfolderindex` (`folderid`,`toid`,`totype`), > KEY `sentitemsindex` (`fromid`,`fromtype`) > ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST > UNION=(`messages2009`); > > > > > > > Sudhir NimavatSenior software engineer. > Quick start global PVT LTD. > Baroda - 390007 > Gujarat, India > > Personally I'm always ready to learn, although I do not always like being > taught > > > The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. > http://in.yahoo.com/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
