How to PARTITION a table in mysql? I read a lot of articles that PARTITIONing is not helpful in most cases. I believe it should speed up the queries in my case. I have a table with the structure of

CREATE TABLE pages
(
page_id int(11) unsigned NOT NULL AUTO_INCREMENT,
category_id smallint(5) unsigned,
title varchar(255),
created datetime,
updated datetime,
FOREIGN KEY(category_id) REFERENCES categories(category_id) ON DELETE CASCADE,
UNIQUE INDEX (category_id,title),
INDEX(title),
PRIMARY KEY(page_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci ROW_FORMAT=COMPRESSED


The table is close to 1 billion rows and 200-1000 category_id.

Almost all queries have category_id in it.

I consider PARTITIONing the table as

PARTITION BY KEY(category_id)
PARTITIONS 40; // between 20-50


Is it worthy?
I have bad news with regard to this table: MySQL does not support Partitioning of a Table with Foreign Keys

According to MySQL 5.7 Docs on Partitioning Limitations


  InnoDB storage engine.  InnoDB foreign keys and MySQL partitioning are not compatible. Partitioned InnoDB tables cannot have foreign key references, nor can they have columns referenced by foreign keys. InnoDB tables which have or which are referenced by foreign keys cannot be partitioned.