List:General Discussion« Previous MessageNext Message »
From:Jim Lyons Date:December 14 2009 8:22pm
Subject:Re: Optimization suggestions
View as plain text  
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

Thread
Optimization suggestionsSudhir N14 Dec
  • Re: Optimization suggestionsJim Lyons14 Dec
  • RE: Optimization suggestionsGavin Towey14 Dec
    • Re: Optimization suggestionssudhir543-nimavat15 Dec