MySQL Forums :: Partitioning :: Partition by threadid or postid?


Advanced Search

Partition by threadid or postid?
Posted by: Lam Vu Nhat ()
Date: January 11, 2011 03:01AM

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

Options: ReplyQuote


Subject Views Written By Posted
Partition by threadid or postid? 2310 Lam Vu Nhat 01/11/2011 03:01AM
Re: Partition by threadid or postid? 1265 Rick James 01/12/2011 10:29AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.