Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Wednesday, 31 January 2018

MySQL Replication Troubleshooting:Show Slave Status working but master data not written in relay logs

Sometimes we are facing the issue with Master-Master / Master-Slave replication like Show Slave Status command showing without any error but really no such master data is replicated on slave server.
MASTER_HEARTBEAT_PERIOD=1


​​Clarification:

In this issue exactly master data not written in relay logs due to somehow Slave IO threads stuck that's why facing such type of issue .

In this case we can define above parameter in our My.ini/My.cnf file .
​MASTER_HEATBEAT_PERIOD is a value in seconds in the range between 0 to 4294967 with resolution in milliseconds. After the loss of a beat the SLAVE IO 
​thread will disconnect and try to connect again.​

​Note: This parameter is configure in master server. ​

Saturday, 27 January 2018

MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT ,HASH )


Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees.

B-tree
A tree data structure that is popular for use in database indexes. The structure is kept sorted at all times, enabling fast lookup for exact matches (equals operator) and ranges (for example, greater than, less than, and BETWEEN operators). This type of index is available for most storage engines, such as InnoDB and MyISAM.
In B-Tree indexes, all the index column values are stored in shorted form.

Because B-tree nodes can have many children, a B-tree is not the same as a binary tree, which is limited to 2 children per node.




PRIMARY KEY - Index column must be assigned as Primary Key.
UNIQUE - Index column must store unique values.
INDEX - Refers to a normal non-unique index. Non-distinct values for the index are allowed, so the index may contain rows with identical values in all columns of the index.
FULLTEXT - Indexes are different from all of the above, and their behaviour differs significantly between database systems. FULLTEXT indexes are only useful for full text searches done with the MATCH() / AGAINST() clause, unlike the above three - which are typically implemented internally using b-trees (allowing for selecting, sorting or ranges starting from left most column) or hash tables (allowing for selection starting from left most column).
Where the other index types are general-purpose, a FULLTEXT index is specialized, in that it serves a narrow purpose: it's only used for a "full text search" feature.

Hash Index-
Contrast with hash index, which is only available in the MEMORY storage engine. The MEMORY storage engine can also use B-tree indexes, and you should choose B-tree indexes for MEMORY tables if some queries use range operators.
Hash index is used for Equal(=) operator only . In Hash Index, each index column values are stored in there hash code with shorted form. 
The use of the term B-tree is intended as a reference to the general class of index design. B-tree structures used by MySQL storage engines may be regarded as variants due to sophistication not present in a classic B-tree design. For related information, refer to the InnoDB Page Structure Fil Header section of the MySQL Internals Manual.

x

Wednesday, 24 January 2018

Alter table operation in huge MySQL production table without table locking

Alter table Add/drop/Modify Column , Create/Drop INDEX in large database without any downtime:

MySQL 5.6: You can now perform read and write operations while an Alter Table operation is being perform even with InnoDB tables - http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

In MySQL 5.6 and higher, the table remains available for read and write operations while the index is being created or dropped. The CREATE INDEX or DROP INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table. Previously, modifying the table while an index is being created or dropped typically resulted in a deadlock that cancelled the INSERT, UPDATE, or DELETE statement on the table.

MySQL 5.6 and higher has introduce Online DDL, this feature becomes more general: you can read and write to tables while an index is being created, and many more kinds of ALTER TABLE operations can be performed without copying the table, without blocking DML operations, or both. Thus in MySQL 5.6 and higher, we typically refer to this set of features as online DDL rather than Fast Index Creation.

Example:-

ALTER TABLE getmysql .session ADD INDEX time_idx(timestamp), ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE `session` ADD `session_count` TINYINT(1) DEFAULT NULL, ALGORITHM=INPLACE, LOCK=NONE;


Tuesday, 23 January 2018

What is MySQL Partitioning?

Partitioning (a database design technique) improves performance, manageability, simplifies maintenance and reduce the cost of storing large amounts of data. Partitioning can be achieved without splitting tables by physically putting tables on individual disk drives. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, therefore queries that access only a fraction of the data can run faster because there is fewer data to scan. There are two major forms of partitioning :
  • Horizontal Partitioning : Horizontal partitioning divides table rows into multiple partitions (based on a logic). All columns defined to a table are found in each partition, so no actual table attributes are missing. All the partition can be addressed individually or collectively. For example, a table that contains whole year sale transaction being partitioned horizontally into twelve distinct partitions, where each partition contains one month's data.
  • Vertical Partitioning : Vertical partitioning divides a table into multiple tables that contain fewer columns. Like horizontal partitioning, in vertical partitioning a query scan fewer data which increases query performance. For example, a table that contains a number of very wide text or BLOB columns that aren't addressed often being broken into two tables that have the most referenced columns in one table and the text or BLOB data in another.
MySQL partitioning
Version: MySQL 5.6
MySQL supports basic table partitioning but does not support vertical partitioning ( MySQL 5.6). This section describes in detail how to implement partitioning as part of your database.
By checking the output of the SHOW PLUGINS statement you will be sure whether your MySQL server supports the partition or not. See the following output :
Sample Output:
mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+---------+---------+
| Name                       | Status   | Type               | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MySQL_native_password      | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| MySQL_old_password         | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| ---------------------------| ------   | ------------------ | ----    | ---     |
| ---------------------------| ------   | ------------------ | ----    | ---     |
| ---------------------------| ------   | ------------------ | ----    | ---     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
+----------------------------+----------+--------------------+---------+---------+
42 rows in set (0.21 sec)
MySQL 5.6 Community binaries include partitioning support.
Enable and disable partitioning support :
  • To enable partitioning (if you are compiling MySQL 5.6 from source), the build must be configured with the -DWITH_PARTITION_STORAGE_ENGINE option.
  • To disable partitioning support, you can start the MySQL Server with the --skip-partition option, in which case the value of have_partitioning is DISABLED.
How to partition a table?
In MySQL you can partition a table using CREATE TABLE or ALTER TABLE command. See the following CREATE TABLE syntax :
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
partition_options:
PARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY(column_list)
| RANGE(expr)
| LIST(expr) }
[PARTITIONS num]
[SUBPARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY(column_list) }
[SUBPARTITIONS num]
]
[(partition_definition[, partition_definition] ...)
partition_definition:
PARTITION partition_name
[VALUES
{LESS THAN {(expr) | MAXVALUE}
|
IN (value_list)}]
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'comment_text' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[NODEGROUP [=] node_group_id]
[(subpartition_definition [, subpartition_definition] ...)]
subpartition_definition:
SUBPARTITION logical_name
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'comment_text' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[NODEGROUP [=] node_group_id]
ALTER TABLE: Partition operations
ALTER TABLE statement can be used for adding, dropping, merging, and splitting partitions, and for performing partitioning maintenance. Here we have defined a nonpartitioned table:
CREATE TABLE sale_mast (
bill_no INT,
bill_date DATETIME
);

This table can be partitioned by HASH (or in another type), using the bill_no column as the partitioning key, into 6 (or other) partitions using ALTER TABLE statement :
ALTER TABLE t1
    PARTITION BY HASH(id)
    PARTITIONS 6;

Partition naming :
Names of partitions follow the rules of other MySQL identifiers such as databases, tables, constraint, stored procedure etc. Partition names are not case-sensitive.
Advantages of partitioning
  • During the scan operation, MySQL optimizer accesses those partitions that will satisfy a particular query. For example, a whole year sale records table may be broken up into 4 partitions (i.e. sale data from of Apr-Jun (partition p0), Jul-Sep (partition p1) , Oct-Dec (partition p2), Jam-Mar (partition p0)) . If a query is issued that contains sale data between Jul-Sep quarter, then it scans the partition p1 only instead of total table records and the query will complete much sooner.
  • Partitioning allows you to have more control over how data is managed inside the database. For example, you can drop specific partitions in a partitioned table where data loses its usefulness. The process of adding new data, in some cases, be greatly facilitated by adding one or more new partitions for storing that data using ALTER TABLE command.
  • In partitioning, it is possible to store more data in one table than can be held on a single disk or file system partition.
  • MySQL 5.6 supports explicit partition selection for queries. For example, SELECT * FROM table1 PARTITION (p0,p1) WHERE col1< 10 selects only those rows in partitions p0 and p1 that match the WHERE condition, this can greatly speed up queries
  • Partition selection also supports the data modification statements DELETE, INSERT, REPLACE, UPDATE, and LOAD DATA, LOAD XML.
Types of MySQL partitioning
Following types of partitioning are available in MySQL 5.6 :
  • RANGE Partitioning
  • LIST Partitioning
  • COLUMNS Partitioning
  • HASH Partitioning
  • KEY Partitioning
  • Subpartitioning
MySQL RANGE Partitioning
In MySQL, RANGE partitioning mode allows us to specify various ranges for which data is assigned. Ranges should be contiguous but not overlapping, and are defined using the VALUES LESS THAN operator. In the following example, sale_mast table contains four columns bill_no, bill_date, cust_code and amount. This table can be partitioned by range in various of ways, depending on your requirement. Here we have used the bill_date column and decide to partition the table 4 ways by adding a PARTITION BY RANGE clause. In these partitions the range of the sale date (sale_date) are as of follow :
  • partition p0 ( sale between 01-01-2013 to 31-03-2013)
  • partition p1 ( sale between 01-04-2013 to 30-06-2013)
  • partition p2 ( sale between 01-07-2013 to 30-09-2013)
  • partition p3 ( sale between 01-10-2013 to 30-12-2013)
Let create the table :
mysql> CREATE TABLE sale_mast (bill_no INT NOT NULL, bill_date TIMESTAMP NOT NULL,
cust_code VARCHAR(15) NOT NULL, amount DECIMAL(8,2) NOT NULL) 
PARTITION BY RANGE (UNIX_TIMESTAMP(bill_date))(
PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2013-04-01')),
PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2013-07-01')),
PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2013-10-01')),
PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2014-01-01')));
Query OK, 0 rows affected (1.50 sec)
Now insert some records in sale_mast table :
mysql> INSERT INTO sale_mast VALUES (1, '2013-01-02', 'C001', 125.56),
(2, '2013-01-25', 'C003', 456.50),
(3, '2013-02-15', 'C012', 365.00),
(4, '2013-03-26', 'C345', 785.00),
(5, '2013-04-19', 'C234', 656.00),
(6, '2013-05-31', 'C743', 854.00),
(7, '2013-06-11', 'C234', 542.00),
(8, '2013-07-24', 'C003', 300.00),
(8, '2013-08-02', 'C456', 475.20);
Query OK, 9 rows affected (0.07 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM sale_mast;
+---------+---------------------+-----------+--------+
| bill_no | bill_date           | cust_code | amount |
+---------+---------------------+-----------+--------+
|       1 | 2013-01-02 00:00:00 | C001      | 125.56 |
|       2 | 2013-01-25 00:00:00 | C003      | 456.50 |
|       3 | 2013-02-15 00:00:00 | C012      | 365.00 |
|       4 | 2013-03-26 00:00:00 | C345      | 785.00 |
|       5 | 2013-04-19 00:00:00 | C234      | 656.00 |
|       6 | 2013-05-31 00:00:00 | C743      | 854.00 |
|       7 | 2013-06-11 00:00:00 | C234      | 542.00 |
|       8 | 2013-07-24 00:00:00 | C003      | 300.00 |
|       9 | 2013-08-02 00:00:00 | C456      | 475.20 |
+---------+---------------------+-----------+--------+
9 rows in set (0.00 sec)
Here is the partition status of sale_mast table:
mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='sale_mast';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          4 |
| p1             |          3 |
| p2             |          2 |
| p3             |          0 |
+----------------+------------+
4 rows in set (0.02 sec)
In the above way you can partition the table based on sale amount (amount. In these partitions the range of the sale amount (amount) are as of follow :
  • partition p0 ( sale amount < 100 )
  • partition p1 ( sale amount < 500 )
  • partition p2 ( sale amount <1000 )
  • partition p3 ( sale amount<1500 )
Let create the table :
mysql> CREATE TABLE sale_mast1 (bill_no INT NOT NULL, bill_date TIMESTAMP NOT NULL,
cust_codE VARCHAR(15) NOT NULL, amount INT NOT NULL)
PARTITION  BY RANGE (amount) (
PARTITION p0 VALUES LESS THAN (100),
PARTITION  p1 VALUES LESS THAN (500),
PARTITION p2 VALUES LESS THAN (1000),
PARTITION  p3 VALUES LESS THAN (1500));
Query OK, 0 rows affected (1.34 sec)
Drop a MySQL partition
If you feel some data are useless in a partitioned table you can drop one or more partition(s). To delete all rows from partition p0 of sale_mast, you can use the following statement :
MySQL> ALTER TABLE sale_mast TRUNCATE PARTITION p0;
Query OK, 0 rows affected (0.49 sec)

mysql> SELECT * FROM sale_mast;
+---------+---------------------+-----------+--------+
| bill_no | bill_date           | cust_code | amount |
+---------+---------------------+-----------+--------+
|       5 | 2013-04-19 00:00:00 | C234      | 656.00 |
|       6 | 2013-05-31 00:00:00 | C743      | 854.00 |
|       7 | 2013-06-11 00:00:00 | C234      | 542.00 |
|       8 | 2013-07-24 00:00:00 | C003      | 300.00 |
|       9 | 2013-08-02 00:00:00 | C456      | 475.20 |
+---------+---------------------+-----------+--------+
5 rows in set (0.01 sec)
Here is the partition status of sale_mast after dropping the partition p0 :
MySQL> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME='sale_mast';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          3 |
| p2             |          2 |
| p3             |          0 |
+----------------+------------+
4 rows in set (0.05 sec)
MySQL LIST Partitioning
List partition allows us to segment data based on a pre-defined set of values (e.g. 1, 2, 3). This is done by using PARTITION BY LIST(expr) where expr is a column value and then defining each partition by means of a VALUES IN (value_list), where value_list is a comma-separated list of integers. In MySQL 5.6, it is possible to match against only a list of integers (and possibly NULL) when partitioning by LIST. In the following example, sale_mast2 table contains four columns bill_no, bill_date, agent_code, and amount. Suppose there are 11 agents represent three cities A, B, C these can be arranged in three partitions with LIST Partitioning as follows :
City
Agent ID
A
1, 2, 3
B
4, 5, 6
C
7, 8, 9, 10, 11
Let create the table :
mysql> CREATE TABLE sale_mast2 (bill_no INT NOT NULL, bill_date TIMESTAMP NOT NULL,
agent_codE INT NOT NULL, amount INT NOT NULL)
PARTITION  BY LIST(agent_code) (
PARTITION pA VALUES IN (1,2,3),
PARTITION pB VALUES IN (4,5,6),
PARTITION pC VALUES IN (7,8,9,10,11));
Query OK, 0 rows affected (1.17 sec)
MySQL COLUMNS Partitioning
In COLUMNS partitioning it is possible to use multiple columns in partitioning keys. There are two types of COLUMNS partitioning :
In addition, both RANGE COLUMNS partitioning and LIST COLUMNS partitioning support the use of non-integer columns for defining value ranges or list members. The permitted data types are shown in the following list:
Both RANGE COLUMNS partitioning and LIST COLUMNS partitioning support following data types for defining value ranges or list members.
  • All integer types: TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER), and BIGINT.
  • DATE and DATETIME.
RANGE COLUMNS partitioning
RANGE COLUMNS partitioning is similar to range partitioning with some significant difference. RANGE COLUMNS accepts a list of one or more columns as partition keys. You can define the ranges using various columns of types (mentioned above) other than integer types.
Here is the basic syntax for creating a table partitioned by RANGE COLUMNS :
CREATE TABLE table_name
PARTITIONED BY RANGE COLUMNS(column_list) (
    PARTITION partition_name VALUES LESS THAN (value_list)[,
    PARTITION partition_name VALUES LESS THAN (value_list)][,
    ...]
)

column_list:
    column_name[, column_name][, ...]

value_list:
    value[, value][, ...]
  • column_list is a list of one or more columns.
  • value_list is a list of values and must be supplied for each partition definition.
  • column list and in the value list defining each partition must occur in the same order
  • The order of the column names in the partitioning column list and the value lists do not have to be the same as the order of the table column definitions in CREATE TABLE statement.
Here is an example :
mysql> CREATE TABLE table3 (col1 INT, col2 INT, col3 CHAR(5), col4 INT)
PARTITION BY RANGE COLUMNS(col1, col2, col3)
 (PARTITION p0 VALUES LESS THAN (50, 100, 'aaaaa'),
 PARTITION p1 VALUES LESS THAN (100,200,'bbbbb'),
 PARTITION p2 VALUES LESS THAN (150,300,'ccccc'),
 PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE));
Query OK, 0 rows affected (1.39 sec)
In the above example -
  • Table table3 contains the columns col1, col2, col3, col4
  • The first three columns have participated in partitioning COLUMNS clause, in the order col1, col2, col3.
  • Each value list used to define a partition contains 3 values in the same order and (INT, INT, CHAR(5)) form.
LIST COLUMNS partitioning
LIST COLUMNS accepts a list of one or more columns as partition keys.You can use various columns of data of types other than integer types as partitioning columns. You can use string types, DATE, and DATETIME columns
In a company there are agents in 3 cities, for sales and marketing purposes. We have organized the agents in 3 cities as shown in the following table :
City
Agent ID
A
A1, A2, A3
B
B1, B2, B3
C
C1, C2, C3, C4, C5
Let create a table with LIST COLUMNS partitioning based on the above information :
mysql> CREATE TABLE salemast ( agent_id VARCHAR(15), agent_name VARCHAR(50),
agent_address VARCHAR(100), city_code VARCHAR(10))
PARTITION BY LIST COLUMNS(agent_id) (
PARTITION pcity_a VALUES IN('A1', 'A2', 'A3'),
PARTITION pcity_b VALUES IN('B1', 'B2', 'B3'),
PARTITION pcity_c VALUES IN ('C1', 'C2', 'C3', 'C4', 'C5'));
Query OK, 0 rows affected (1.06 sec)
You can use DATE and DATETIME columns in LIST COLUMNS partitioning, see the following example :
CREATE TABLE sale_master (bill_no INT NOT NULL, bill_date DATE,
cust_code VARCHAR(15) NOT NULL, amount DECIMAL(8,2) NOT NULL) 
PARTITION BY RANGE COLUMNS (bill_date)(
PARTITION p_qtr1 VALUES LESS THAN ('2013-04-01'),
PARTITION p_qtr2 VALUES LESS THAN ('2013-07-01'),
PARTITION p_qtr3 VALUES LESS THAN ('2013-10-01'),
PARTITION p_qtr4 VALUES LESS THAN ('2014-01-01'));
MySQL HASH Partitioning
MySQL HASH partition is used to distribute data among a predefined number of partitions on a column value or expression based on a column value. This is done by using PARTITION BY HASH(expr) clause, adding in CREATE TABLE STATEMENT. In PARTITIONS num clause, num is a positive integer represents the number of partitions of the table. The following statement creates a table that uses hashing on the studetn_id column and is divided into 4 partitions :
MySQL>CREATE TABLE student (student_id INT NOT NULL,
class VARCHAR(8), name VARCHAR(40),
date_of_admission DATE NOT NULL DEFAULT '2000-01-01')
PARTITION BY HASH(student_id)
PARTITIONS 4;
Query OK, 0 rows affected (1.43 sec)

It is also possible to make a partition based on the year in which a student was admitted. See the following statement :

MySQL> CREATE TABLE student (student_id INT NOT NULL,
class VARCHAR(8), class VARCHAR(8), name VARCHAR(40),
date_of_admission DATE NOT NULL DEFAULT '2000-01-01')
PARTITION BY HASH(YEAR(date_of_admission))
PARTITIONS 4;
Query OK, 0 rows affected (1.27 sec)

MySQL KEY Partitioning
MySQL KEY partition is a special form of HASH partition, where the hashing function for key partitioning is supplied by the MySQL server. The server employs its own internal hashing function which is based on the same algorithm as PASSWORD(). This is done by using PARTITION BY KEY, adding in CREATE TABLE STATEMENT. In KEY partitioning KEY takes only a list of zero or more column names. Any columns used as the partitioning key must comprise part or all of the table's primary key if the table has one. If there is a primary key in a table, it is used as partitioning key when no column is specified as the partitioning key. Here is an example :
MySQL> CREATE TABLE table1 ( id INT NOT NULL PRIMARY KEY,
fname  VARCHAR(25), lname VARCHAR(25))
PARTITION BY KEY()
PARTITIONS 2;
Query OK, 0 rows affected (0.84 sec)
If there is no primary key but there is a unique key in a table, then the unique key is used for the partitioning key :
MySQL> CREATE TABLE table2 ( id INT NOT NULL, fname  VARCHAR(25),
lname VARCHAR(25),
UNIQUE KEY (id))
PARTITION BY KEY()
PARTITIONS 2;
Query OK, 0 rows affected (0.77 sec)
MySQL Subpartitioning
Subpartitioning is a method to divide each partition further in a partitioned table. See the following CREATE TABLE statement :
CREATE TABLE table10 (BILL_NO INT, sale_date DATE, cust_code VARCHAR(15),
AMOUNT DECIMAL(8,2))
PARTITION BY RANGE(YEAR(sale_date) )
SUBPARTITION BY HASH(TO_DAYS(sale_date))
SUBPARTITIONS 4 (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN (2010),
PARTITION p3 VALUES LESS THAN MAXVALUE
);

In the above statement -
  • The table has 4 RANGE partitions.
  • Each of these partitions—p0, p1, p2 and p3—is further divided into 4 subpartitions.
  • Therefore the entire table is divided into 4 * 4 = 16 partitions.
Here is the partition status of table10 :
mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM
INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='stable';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p0             |          0 |
| p0             |          0 |
| p0             |          0 |
| p1             |          0 |
| p1             |          0 |
| p1             |          0 |
| p1             |          0 |
| p2             |          0 |
| p2             |          0 |
| p2             |          0 |
| p2             |          0 |
| p3             |          0 |
| p3             |          0 |
| p3             |          0 |
| p3             |          0 |
+----------------+------------+
16 rows in set (0.16 sec)

Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...