Partitioning

User-defined partitioning (refer to http://dev.mysql.com/doc/refman/5.1/en/partitioning.html) is offered in MySQL 5.1. It allows us to "distribute portions of individual tables across a file system according to rules which you can set largely as needed". Using this feature in phpMyAdmin requires knowledge of its syntax as there are many partition types. Also, for each partition type, the number of partitions and the values associated with each partition are too random to be easily represented on a web interface.

Creating a table with partitions

Let us try it by creating a table named test with one column id. On the table creation panel, if connected to a MySQL 5.1 server, phpMyAdmin shows a PARTITION definition dialog, as shown in the following screenshot:

Creating a table with partitions

Here, we enter a PARTITION BY RANGE clause, which will create partitions on the id column:

PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (1000),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN (30000)
);

Maintaining partitions

For a table on which a partition has been defined, the Operations page displays a Partition maintenance dialog where we can:

  • Choose a partition and then request an action, such as Rebuild
  • Remove the partitioning
Maintaining partitions

Exporting a partition definition

Finally, exporting this test table in SQL mode produces statements with embedded comments that a MySQL 5.1 server would recognize and interpret in order to recreate the same partitions: 

CREATE TABLE `test` (
`id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (1000) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (2000) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (3000) ENGINE = MyISAM) */;
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.12.107.31