How to create mysql partitions

The website become very slow because there are too much data on mysql tables. This is the common problem for sites which save historical data. I just found this issue on my website, then googling to find solutions for this. And then, yap, there's simple solution for handling large table issues.

Why should you create Mysql Partitions?

I'd like to illustrate like this: There are 10,000 records, for example, in post table. Without partitioning, you will scan all of those rows even if you selecting only one record.

SELECT * FROM `post` WHERE `ID`='120'

The query above will scan all records in table. So if you have million records, you will find your site running slow. Assume that we will record data based on date created. For instance, it has `date` column. Then it's good idea to set partition by range date. I'd like to show you how I implement mysql partition for table 'post'.

Creating MySQL Partitions

CREATE TABLE IF NOT EXISTS `post` (
  `ID` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `title` char(225) NOT NULL,
  `content` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/* PARTITION BY RANGE ( YEAR(`date`))
   PARTITION p0 VALUES LESS THAN (2014),
   PARTITION p1 VALUES LESS THAN (2015)
*/

Or, if you already have the table:

ALTER TABLE `post` PARTITION BY RANGE ( YEAR(`date`))
   PARTITION p0 VALUES LESS THAN (2014),
   PARTITION p1 VALUES LESS THAN (2015)   

Partition Selection

So far we have 2 partitions. `p0` will record posts that created on 2013 and `p1` contains posts crated on 2014. Now we can perform mysql query to see if it works:

SELECT * FROM `post` PARTITION `p0` WHERE `ID`='120'; // in MySQL 5.6

SELECT * FROM `post` WHERE `date` BETWEEN '2013-01-01' AND '2013-12-32' AND `ID`='120'; // MySQL 5.1+

No problem if there are million records, the query above will scan only partition `p0` in post table. And of course this optimization improves your mysql query. Don't stop only on this page. Go ahead to learn more about MySQL Partitioning.