Partition by threadid or postid?
Hi all,
CREATE TABLE `post` (
`postid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`threadid` int(10) unsigned NOT NULL DEFAULT '0',
`parentid` int(10) unsigned NOT NULL DEFAULT '0',
`username` varchar(100) NOT NULL DEFAULT '',
`userid` int(10) unsigned NOT NULL DEFAULT '0',
`title` varchar(250) NOT NULL DEFAULT '',
`dateline` int(10) unsigned NOT NULL DEFAULT '0',
`pagetext` mediumtext,
`allowsmilie` smallint(6) NOT NULL DEFAULT '0',
`showsignature` smallint(6) NOT NULL DEFAULT '0',
`ipaddress` char(15) NOT NULL DEFAULT '',
`iconid` smallint(5) unsigned NOT NULL DEFAULT '0',
`visible` smallint(6) NOT NULL DEFAULT '0',
`attach` smallint(5) unsigned NOT NULL DEFAULT '0',
`infraction` smallint(5) unsigned NOT NULL DEFAULT '0',
`reportthreadid` int(10) unsigned NOT NULL DEFAULT '0',
`post_thanks_amount` int(10) unsigned NOT NULL DEFAULT '0',
`validated` tinyint(2) NOT NULL DEFAULT '0',
PRIMARY KEY (`postid`),
KEY `threadid` (`threadid`,`visible`,`dateline`),
KEY `userid` (`userid`,`parentid`,`validated`)
) ENGINE=MyISAM AUTO_INCREMENT=17881987 DEFAULT CHARSET=utf8
record number: 11000000
Here are some slow queries:
1, SELECT postid FROM post WHERE threadid = $threadid ...
2, SELECT * FROM post WHERE postid = $postid
3, UPDATE post SET ... WHERE postid = $postid
4, INSERT INTO post ...
I want to partition the table by key. Then I wondered as to partition threadid or postid