I have a very large table approx 28GB and that is continually increasing. I am thinking about partitioning my table.
The table is InnoDB and The File Per Table is enabled
I have a field name created
which is an integer field and stores the timestamp so I was thinking about creating the table like this:
ALTER TABLE TABLE_NAME PARTITION BY RANGE (created)
(
PARTITION p0 VALUES LESS THAN (1325356200) ENGINE = InnoDB, # Data before 2012
PARTITION p1 VALUES LESS THAN (1333218600) ENGINE = InnoDB, # Data for JAN,FEB,MARCH YEAR 2012
PARTITION p2 VALUES LESS THAN (1341081000) ENGINE = InnoDB, # NEXT THREE MONTH DATA FOR YEAR 2012
PARTITION p3 VALUES LESS THAN (1349029800) ENGINE = InnoDB, # NEXT THREE MONTH DATA FOR YEAR 2012
PARTITION p4 VALUES LESS THAN (1356978600) ENGINE = InnoDB, # NEXT THREE MONTH DATA FOR YEAR 2012
PARTITION p5 VALUES LESS THAN MAXVALUE # DATA for Next years
) ;
I have partitioned the year 2012 in Quarters(As it is our requirement) and will also do the same for year 2013 but not done here.
Questions:
- Does partitioning boost INSERTs and SELECTs Performance
- How much time the ALTER take as it is a huge amount of Data(Any way to minimize the Alter time)?
- Do I need to use the field created in the queries so that the optimizer can use the partition?
- Are there any limitation's or risks by partioning this table?
- Is there any way to ALTER the table without downtime?