Partition table MyISAM vs InnoDB

Benchmark partition table with MyISAM & InnoDB engine

Partitions table with MyISAM:
Load data 1.000.000 rows
mysql> call load_test_part_myisam();
Query OK, 1 row affected (57.69 sec)

mysql> select count(*) from test_part_myisam where c3 > date ‘2000-01-01’ and c3 < date '2000-12-31';
+———-+
| count(*) |
+———-+
| 99681 |
+———-+
1 row in set (0.10 sec)

Partitions table with InnoDB:
Load data 1.000.000 rows
mysql> call load_test_part_innodb();
Query OK, 1 row affected (1 hour 11 min 25.19 sec)

mysql> select count(*) from test_part_innodb where c3 > date ‘2000-01-01’ and c3 < date '2000-12-31';
+———-+
| count(*) |
+———-+
| 99681 |
+———-+
1 row in set (0.22 sec)

Create table MyISAM with Partitions

mysql> CREATE TABLE test_part_myisam
-> ( c1 int default NULL,
-> c2 varchar(30) default NULL,
-> c3 date default NULL
->
-> ) engine=MyISAM
-> PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (2000),
-> PARTITION p1 VALUES LESS THAN (2001) , PARTITION p2 VALUES LESS THAN (2002) ,
-> PARTITION p3 VALUES LESS THAN (2003) , PARTITION p4 VALUES LESS THAN (2004) ,
-> PARTITION p5 VALUES LESS THAN (2005) , PARTITION p6 VALUES LESS THAN (2006) ,
-> PARTITION p7 VALUES LESS THAN (2007) , PARTITION p8 VALUES LESS THAN (2008) ,
-> PARTITION p9 VALUES LESS THAN (2009) , PARTITION p10 VALUES LESS THAN (2010),
-> PARTITION p99 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.03 sec)

mysql> show create table test_part_myisam\G;
*************************** 1. row ***************************
Table: test_part_myisam
Create Table: CREATE TABLE `test_part_myisam` (
`c1` int(11) DEFAULT NULL,
`c2` varchar(30) DEFAULT NULL,
`c3` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (year(c3))
(PARTITION p0 VALUES LESS THAN (2000) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (2001) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (2002) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (2003) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (2004) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (2005) ENGINE = MyISAM,
PARTITION p6 VALUES LESS THAN (2006) ENGINE = MyISAM,
PARTITION p7 VALUES LESS THAN (2007) ENGINE = MyISAM,
PARTITION p8 VALUES LESS THAN (2008) ENGINE = MyISAM,
PARTITION p9 VALUES LESS THAN (2009) ENGINE = MyISAM,
PARTITION p10 VALUES LESS THAN (2010) ENGINE = MyISAM,
PARTITION p99 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
1 row in set (0.00 sec)

mysql> select count(*) from test_part_myisam where c3 > date '2000-01-01' and c3 call load_test_part_myisam();
Query OK, 1 row affected (57.69 sec)

mysql> select count(*) from test_part_myisam where c3 > date '2000-01-01' and c3 < date '2000-12-31';
+----------+
| count(*) |
+----------+
| 99681 |
+----------+
1 row in set (0.10 sec)

[mysql@mysqlddb1 test]$ mysqldump -u root -p test test_part_myisam > test_part_myisam.sql

mysql> alter table test_part_myisam drop partition p1;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table test_part_myisam\G;
*************************** 1. row ***************************
Table: test_part_myisam
Create Table: CREATE TABLE `test_part_myisam` (
`c1` int(11) DEFAULT NULL,
`c2` varchar(30) DEFAULT NULL,
`c3` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (year(c3))
(PARTITION p0 VALUES LESS THAN (2000) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (2002) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (2003) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (2004) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (2005) ENGINE = MyISAM,
PARTITION p6 VALUES LESS THAN (2006) ENGINE = MyISAM,
PARTITION p7 VALUES LESS THAN (2007) ENGINE = MyISAM,
PARTITION p8 VALUES LESS THAN (2008) ENGINE = MyISAM,
PARTITION p9 VALUES LESS THAN (2009) ENGINE = MyISAM,
PARTITION p10 VALUES LESS THAN (2010) ENGINE = MyISAM,
PARTITION p99 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
1 row in set (0.00 sec)

mysql> select count(*) from test_part_myisam where c3 > date '2000-01-01' and c3 < date '2000-12-31';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.10 sec)

[mysql@mysqlddb1 test]$ mysql -u root -p test < test_part_myisam.sql

mysql> select count(*) from test_part_myisam where c3 > date '2000-01-01' and c3 show create table test_part_myisam\G;
*************************** 1. row ***************************
Table: test_part_myisam
Create Table: CREATE TABLE `test_part_myisam` (
`c1` int(11) DEFAULT NULL,
`c2` varchar(30) DEFAULT NULL,
`c3` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (year(c3))
(PARTITION p0 VALUES LESS THAN (2000) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (2001) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (2002) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (2003) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (2004) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (2005) ENGINE = MyISAM,
PARTITION p6 VALUES LESS THAN (2006) ENGINE = MyISAM,
PARTITION p7 VALUES LESS THAN (2007) ENGINE = MyISAM,
PARTITION p8 VALUES LESS THAN (2008) ENGINE = MyISAM,
PARTITION p9 VALUES LESS THAN (2009) ENGINE = MyISAM,
PARTITION p10 VALUES LESS THAN (2010) ENGINE = MyISAM,
PARTITION p99 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
1 row in set (0.00 sec)

Test REORGANIZE PARTITION

mysql> alter table test_part_myisam drop partition p1;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table test_part_myisam\G;
*************************** 1. row ***************************
Table: test_part_myisam
Create Table: CREATE TABLE `test_part_myisam` (
`c1` int(11) DEFAULT NULL,
`c2` varchar(30) DEFAULT NULL,
`c3` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (year(c3))
(PARTITION p0 VALUES LESS THAN (2000) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (2002) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (2003) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (2004) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (2005) ENGINE = MyISAM,
PARTITION p6 VALUES LESS THAN (2006) ENGINE = MyISAM,
PARTITION p7 VALUES LESS THAN (2007) ENGINE = MyISAM,
PARTITION p8 VALUES LESS THAN (2008) ENGINE = MyISAM,
PARTITION p9 VALUES LESS THAN (2009) ENGINE = MyISAM,
PARTITION p10 VALUES LESS THAN (2010) ENGINE = MyISAM,
PARTITION p99 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
1 row in set (0.00 sec)

mysql> select count(*) from test_part_myisam where c3 > date '2000-01-01' and c3 ALTER TABLE test_part_myisam REORGANIZE PARTITION p2 into ( PARTITION p1 VALUES LESS THAN (2001),PARTITION p2 VALUES LESS THAN (2002) );
Query OK, 99954 rows affected (1 min 1.24 sec)
Records: 99954 Duplicates: 0 Warnings: 0

mysql> show create table test_part_myisam\G;
*************************** 1. row ***************************
Table: test_part_myisam
Create Table: CREATE TABLE `test_part_myisam` (
`c1` int(11) DEFAULT NULL,
`c2` varchar(30) DEFAULT NULL,
`c3` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (year(c3))
(PARTITION p0 VALUES LESS THAN (2000) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (2001) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (2002) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (2003) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (2004) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (2005) ENGINE = MyISAM,
PARTITION p6 VALUES LESS THAN (2006) ENGINE = MyISAM,
PARTITION p7 VALUES LESS THAN (2007) ENGINE = MyISAM,
PARTITION p8 VALUES LESS THAN (2008) ENGINE = MyISAM,
PARTITION p9 VALUES LESS THAN (2009) ENGINE = MyISAM,
PARTITION p10 VALUES LESS THAN (2010) ENGINE = MyISAM,
PARTITION p99 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
1 row in set (0.01 sec)

mysql> select count(*) from test_part_myisam where c3 > date '2000-01-01' and c3 < date '2000-12-31';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

[mysql@mysqlddb1 test]$ date
Tue Mar 29 12:28:54 WIT 2011
[mysql@mysqlddb1 test]$ mysql -u root -p test < test_part_myisam.sql
Enter password:
[mysql@mysqlddb1 test]$ date
Tue Mar 29 12:29:43 WIT 2011

mysql> select count(*) from test_part_myisam where c3 > date '2000-01-01' and c3 < date '2000-12-31';
+----------+
| count(*) |
+----------+
| 99681 |
+----------+
1 row in set (0.11 sec)

Create table MyISAM without partitions

mysql> create table test_nopart_myisam
-> (c1 int(11) default NULL,
-> c2 varchar(30) default NULL,
-> c3 date default NULL) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table test_nopart_myisam\G;
*************************** 1. row ***************************
Table: test_nopart_myisam
Create Table: CREATE TABLE `test_nopart_myisam` (
`c1` int(11) DEFAULT NULL,
`c2` varchar(30) DEFAULT NULL,
`c3` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE load_test_nopart_myisam()
-> begin
-> declare v int default 0;
-> while v do
-> insert into test_nopart_myisam
-> values (v,'testing partitions',adddate('2000-01-01',(rand(v)*36520) mod 3652));
-> set v = v + 1;
-> end while;
-> end
-> //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call load_test_nopart_myisam();
Query OK, 1 row affected (44.22 sec)

mysql> select count(*) from test_nopart_myisam where c3 > date '2000-01-01' and c3 < date '2000-12-31';
+----------+
| count(*) |
+----------+
| 99681 |
+----------+
1 row in set (1.00 sec)

Create table InnoDB with partitions

mysql> CREATE TABLE test_part_innodb
-> ( c1 int default NULL,
-> c2 varchar(30) default NULL,
-> c3 date default NULL
->
-> ) engine=InnoDB
-> PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (2000),
-> PARTITION p1 VALUES LESS THAN (2001) , PARTITION p2 VALUES LESS THAN (2002) ,
-> PARTITION p3 VALUES LESS THAN (2003) , PARTITION p4 VALUES LESS THAN (2004) ,
-> PARTITION p5 VALUES LESS THAN (2005) , PARTITION p6 VALUES LESS THAN (2006) ,
-> PARTITION p7 VALUES LESS THAN (2007) , PARTITION p8 VALUES LESS THAN (2008) ,
-> PARTITION p9 VALUES LESS THAN (2009) , PARTITION p10 VALUES LESS THAN (2010),
-> PARTITION p99 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.13 sec)

mysql> show create table test_part_innodb\G;
*************************** 1. row ***************************
Table: test_part_innodb
Create Table: CREATE TABLE `test_part_innodb` (
`c1` int(11) DEFAULT NULL,
`c2` varchar(30) DEFAULT NULL,
`c3` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (year(c3))
(PARTITION p0 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (2001) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2002) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2003) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (2004) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (2005) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (2006) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (2007) ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN (2008) ENGINE = InnoDB,
PARTITION p9 VALUES LESS THAN (2009) ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN (2010) ENGINE = InnoDB,
PARTITION p99 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE load_test_part_innodb()
-> begin
do
-> declare v int default 0;
-> while v do
-> insert into test_part_innodb
-> values (v,'testing partitions',adddate('2000-01-01',(rand(v)*36520) mod 3652));
-> set v = v + 1;
-> end while;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call load_test_part_innodb();
Query OK, 1 row affected (1 hour 11 min 25.19 sec)

mysql> select count(*) from test_part_innodb where c3 > date '2000-01-01' and c3 < date '2000-12-31';
+----------+
| count(*) |
+----------+
| 99681 |
+----------+
1 row in set (0.22 sec)

Create View for generates partition backup script

mysql> show create view part_backup\G;
*************************** 1. row ***************************
View: part_backup
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `part_backup` AS select

concat('mysqldump',' -u root',' -p ',' --no-create-info',' --where="',(case `p2`.`PARTITION_METHOD` when 'hash' then
concat(`p2`.`PARTITION_EXPRESSION`,' % ',(select count(0) from `information_schema`.`partitions` `p3` where ((`p3`.`TABLE_SCHEMA` =
`p2`.`TABLE_SCHEMA`) and (`p3`.`TABLE_NAME` = `p2`.`TABLE_NAME`))),' = ',(`p2`.`PARTITION_ORDINAL_POSITION` - 1)) when 'list' then
concat(`p2`.`PARTITION_EXPRESSION`,' in (',`p2`.`PARTITION_DESCRIPTION`,')') when 'range' then
concat(if((`p2`.`PARTITION_ORDINAL_POSITION` = 1),'',concat(`p2`.`PARTITION_EXPRESSION`,' >=
',replace(`p1`.`PARTITION_DESCRIPTION`,'MAXVALUE',~(0)),' and ')),concat(`p2`.`PARTITION_EXPRESSION`,'
',`p2`.`TABLE_SCHEMA`,'.',`p2`.`TABLE_NAME`) AS `Name_exp_1` from (`information_schema`.`partitions` `p2` left join
`information_schema`.`partitions` `p1` on(((`p1`.`TABLE_SCHEMA` = `p2`.`TABLE_SCHEMA`) and (`p1`.`TABLE_NAME` = `p2`.`TABLE_NAME`) and

((`p1`.`PARTITION_ORDINAL_POSITION` + 1) = `p2`.`PARTITION_ORDINAL_POSITION`)))) where ((`p2`.`TABLE_SCHEMA` = database()) and
(`p2`.`PARTITION_METHOD` in ('hash','list','range')))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.01 sec)

mysql> desc part_backup;
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| Name_exp_1 | mediumtext | YES | | NULL | |
+------------+------------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> select * from part_backup where Name_exp_1 like'%myisam%';
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Name_exp_1 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| mysqldump -u root -p --no-create-info --where="year(c3) test.test_part_myisam |
| mysqldump -u root -p --no-create-info --where="year(c3) >= 2000 and year(c3) test.test_part_myisam |
| mysqldump -u root -p --no-create-info --where="year(c3) >= 2001 and year(c3) test.test_part_myisam |
| mysqldump -u root -p --no-create-info --where="year(c3) >= 2002 and year(c3) test.test_part_myisam |
| mysqldump -u root -p --no-create-info --where="year(c3) >= 2003 and year(c3) test.test_part_myisam |
| mysqldump -u root -p --no-create-info --where="year(c3) >= 2004 and year(c3) test.test_part_myisam |
| mysqldump -u root -p --no-create-info --where="year(c3) >= 2005 and year(c3) test.test_part_myisam |
| mysqldump -u root -p --no-create-info --where="year(c3) >= 2006 and year(c3) test.test_part_myisam |
| mysqldump -u root -p --no-create-info --where="year(c3) >= 2007 and year(c3) test.test_part_myisam |
| mysqldump -u root -p --no-create-info --where="year(c3) >= 2008 and year(c3) test.test_part_myisam |
| mysqldump -u root -p --no-create-info --where="year(c3) >= 2009 and year(c3) test.test_part_myisam |
| mysqldump -u root -p --no-create-info --where="year(c3) >= 2010 and year(c3) test.test_part_myisam |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
12 rows in set (0.89 sec)

[mysql@mysqlddb1 test]$ mysqldump -u root -p –no-create-info –where=”year(c3) >= 2000 and year(c3) test.test_part_myisam.p1.sql

mysql> select count(*) from test_part_myisam where c3 > date '2000-01-01' and c3 alter table test_part_myisam drop partition p1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE test_part_myisam REORGANIZE PARTITION p2 into ( PARTITION p1 VALUES LESS THAN
-> (2001),PARTITION p2 VALUES LESS THAN (2002) );
Query OK, 99954 rows affected (1 min 0.92 sec)
Records: 99954 Duplicates: 0 Warnings: 0

mysql> select count(*) from test_part_myisam where c3 > date '2000-01-01' and c3 < date '2000-12-31';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

[mysql@mysqlddb1 test]$ mysql -u root -p test < test.test_part_myisam.p1.sql

mysql> select count(*) from test_part_myisam where c3 > date '2000-01-01' and c3 < date '2000-12-31';
+----------+
| count(*) |
+----------+
| 99681 |
+----------+
1 row in set (0.10 sec)

Advertisements

About davidand

Hi, welcome to my blogs. My name is David Andriansyah. I am currently working as an independent Cognos Business Intelligence consultant, and I also as Freelance Web Developer. View all posts by davidand

3 responses to “Partition table MyISAM vs InnoDB

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: