Partitioning a table will boost the performance?

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?

Does partitioning boost INSERTs and SELECTs Performance

It depends. See the PARTITION section of http://mysql.rjweb.org/doc.php/ricksrots


How much time the ALTER take as it is a huge amount of Data(Any way to minimize the Alter time)?

ALTER (usually) takes a time proportional to the data size. Hour(s) for 28GB. OTOH, ALTER PARTITION may be much faster. DROP PARTITION is virtually instantaneous, hence useful for purging 'old' data. Be more specific about your ALTER question.


Do I need to use the field created in the queries so that the optimizer can use the partition?

The rules on indexes may seem screwball. Let's see the CREATE TABLE and the SELECT(s).


Are there any limitation's or risks by partioning this table?

No more than 1024 partitions, but 100 is 'too many', in my opinion. The biggest risk is that you will do all the work to do PARTITIONing, only to get no performance boost.


Is there any way to ALTER the table without downtime?

It depends -- be specific about the ALTER.

StackExchange Database Administrators Q#19313, answer score: 6