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/
