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 <gto...@ffn.com> To: Sudhir N <sudhir_nima...@yahoo.com>; Mysql <mysql@lists.mysql.com> 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_nima...@yahoo.com] 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/