List:General Discussion« Previous MessageNext Message »
From:sudhir543-nimavat Date:December 15 2009 5:52am
Subject:Re: Optimization suggestions
View as plain text  
Tables has varchar36 primary keys, because I use UUID and not auto increment columns.
I have verified all the queries that are going to run on tables using Explain and it show
correct index being used.

Thanks
SN

 
  
    
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
  




________________________________
From: Gavin Towey <gtowey@stripped>
To: Sudhir N <sudhir_nimavat@stripped>; Mysql <mysql@stripped>
Sent: Tue, 15 December, 2009 2:16:53 AM
Subject: RE: Optimization suggestions

Id should probably be an auto_incrementing INT, if you still need a unique text
identifier, then I would make a separate field.  Though my opinion isn't the only way;
there is much debate on natural vs. surrogate keys.

I would normalize "folderid" into a lookup in another table, and make folderid an INT
value.

Threadid is another field that would probably be better as an INT.

As for your indexes, they depend completely on what type of queries you're going to be
running.  Once you know that, then you can test them using sample data and EXPLAIN.

http://dev.mysql.com/doc/refman/5.0/en/explain.html 
http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html 

About normalization:
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html 


Regards,
Gavin Towey


-----Original Message-----
From: Sudhir N [mailto:sudhir_nimavat@stripped]
Sent: Monday, December 14, 2009 10:31 AM
To: Mysql
Subject: Optimization suggestions

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/ 

This message contains confidential information and is intended only for the individual
named.  If you are not the named addressee, you are notified that reviewing,
disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. 
Please notify the sender immediately by e-mail if you have received this e-mail by
mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed
to be secure or error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not
accept liability for any loss or damage caused by viruses or errors or omissions in the
contents of this message, which arise as a result of e-mail transmission. [FriendFinder
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com



      The INTERNET now has a personality. YOURS! See your Yahoo! Homepage.
http://in.yahoo.com/
Thread
Optimization suggestionsSudhir N14 Dec
  • Re: Optimization suggestionsJim Lyons14 Dec
  • RE: Optimization suggestionsGavin Towey14 Dec
    • Re: Optimization suggestionssudhir543-nimavat15 Dec