can i apply mysql partition on this table ?

Question

Hello all

I have this table

CREATE TABLE IF NOT EXISTS `posts` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `thread_id` int(10) unsigned NOT NULL,
  `forum_id` tinyint(5) unsigned NOT NULL,
  `cat_id` tinyint(3) unsigned NOT NULL,
  `message` mediumtext collate utf8_unicode_ci NOT NULL,
  `userid` int(10) unsigned NOT NULL,
  `date` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `thread_id` (`thread_id`),
  KEY `userid` (`userid`),
  KEY `date` (`date`),
  KEY `forum_id` (`forum_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3273548 ;

how can i apply partition on it and what is the best type of partition i can use ?

Query is

SELECT id,user_id,message FROM posts WHERE thread_id = %

with some joins

PS: table is 2.5 GiB

• don't know much about MySQL partition :(

thank you

Answer 1

from the top of my head (without knowing much about your application) I would assume that your highest entity its "forum" I would do a sharding per "forum".

Remember sharding isn't easy

Edit 1

First: 2.5gb of data size isn't much, we work with tables much more big than that.

tips:

• check your indexes
• move form MyISAM to InnoDB -> this will make the queries a little more slow but not blocking.
• before shard try to denormalize