Recently, I was discussing with one of my colleagues about how insert statement performs for MySQL partitioned tables. General prediction is that it should be slower than for non-partitioned tables, but how much that we didn’t know. So, I thought let’s test with different types of partitions (i.e range, list and hash) and also with different number of partitions and check how’s performance. As people says, MySQL 5.7 is must faster than old one, so I also tested partitions with it.

So, I took simple table with 3.2M records on Centos 6.7 VM (4 core with 2GB RAM) with default my.cnf settings and then created tables for range, list and hash partitioning with 5,25,50 and 100 partitions. i.e with 5 partition (range and list), the table structures were like

CREATE TABLE emp_range_5(
 id int,
 fname varchar (30),
 lname varchar (30),
 hired_date date not null,
 separated_date date not null,
 job_code int,
 store_id int
 ) …