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_nimavat@stripped> 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
