MYSQL Partitioning : What is and HOW To ?

The idea behind partitioning isn’t to use multiple servers but to use multiple tables instead of one table. You can divide a table into many tables so that you can have old data in one sub table and new data in another table. Then the database can optimize queries where you ask for new data knowing that they are in the second table. What’s more, you define how the data is partitioned.

Simple example from the MySQL Documentation:

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

This allows to speed up e.g.:

  1. Dropping old data by simple:ALTER TABLE employees DROP PARTITION p0;
  2. Database can speed up a query like this:SELECT COUNT(*) FROM employees WHERE separated BETWEEN ‘2000-01-01’ AND ‘2000-12-31’ GROUP BY store_id;

Knowing that all data is stored only on the p2 partition.

Related Posts

  • 42
    Configure asterisk MySql connection: create the res_mysql.conf file in /etc/asterisk vi /etc/asterisk/res_mysql.conf enter the following: [general] dbhost = 127.0.0.1 dbname = asteriskrealtime dbuser = root dbpass = password dbport = 3306 save the following configuration files, we will use a very short files instead in order to simplefy the setup:…
    Tags: null, default, varchar
  • 31
    If you have to restore MyISAM tables that have become corrupt, try to recover them using REPAIR TABLE  or myisamchk -r That should work in 99.9% of all cases. In this article we will cover to repair/recover MyISAM tables using myisamchk. MyISAM tables have .MYD and .MYI  files for storing data…
    Tags: table, tables, data, mysql

3 thoughts on “MYSQL Partitioning : What is and HOW To ?

  • August 15, 2014 at 6:33 pm
    Permalink

    ALTER TABLE tbl_rtdata PARTITION BY RANGE (Month(fld_date))
    (
    PARTITION p_Apr VALUES LESS THAN (TO_DAYS(‘2012-05-01’)),
    PARTITION p_May VALUES LESS THAN (TO_DAYS(‘2012-06-01’)),
    PARTITION p_Jun VALUES LESS THAN (TO_DAYS(‘2012-07-01’)),
    PARTITION p_Jul VALUES LESS THAN (TO_DAYS(‘2012-08-01’)),
    PARTITION p_Aug VALUES LESS THAN (TO_DAYS(‘2012-09-01’)),
    PARTITION p_Sep VALUES LESS THAN (TO_DAYS(‘2012-10-01’)),
    PARTITION p_Oct VALUES LESS THAN (TO_DAYS(‘2012-11-01’)),
    PARTITION p_Nov VALUES LESS THAN (TO_DAYS(‘2012-12-01’)),
    PARTITION p_Dec VALUES LESS THAN MAXVALUE );

    Reply
  • August 15, 2014 at 6:33 pm
    Permalink

    ALTER TABLE tbl_rtdata PARTITION BY RANGE COLUMNS (fld_date) (
    PARTITION Apr_0 VALUES LESS THAN (‘2012-05-01’),
    PARTITION May_1 VALUES LESS THAN (‘2012-06-01’),
    PARTITION Dec_8 VALUES LESS THAN (MAXVALUE)
    );

    Reply
  • August 15, 2014 at 6:34 pm
    Permalink

    ALTER TABLE tbl_rtdata PARTITION BY RANGE (Month(fld_date))

    SUBPARTITION BY HASH (Day(fld_date)) SUBPARTITIONS 12(
    PARTITION Apr_0 VALUES LESS THAN (2012-05-01),
    PARTITION May_1 VALUES LESS THAN (2012-06-01),
    PARTITION Jun_2 VALUES LESS THAN (2012-07-01),
    PARTITION Jul_3 VALUES LESS THAN (2012-08-01),
    PARTITION Aug_4 VALUES LESS THAN (2012-09-01),
    PARTITION Sep_5 VALUES LESS THAN (2012-10-01),
    PARTITION Oct_6 VALUES LESS THAN (2012-11-01),
    PARTITION Nov_7 VALUES LESS THAN (2012-12-01),
    PARTITION Dec_8 VALUES LESS THAN MAXVALUE );

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: