content_image/861182.jpg
## Setting MERGE_THRESHOLD for Individual Indexes 

ALTER TABLE t1 COMMENT=’MERGE_THRESHOLD=40’; 

To set the MERGE_THRESHOLD  value for an individual index, you can use the index_option COMMENT clause with CREATE TABLE , ALTER TABLE , or CREATE INDEX , as shown in the following examples: 

## Querying the MERGE_THRESHOLD Value for an Index 

•Setting MERGE_THRESHOLD  for an individual index using CREATE TABLE : 

CREATE TABLE t1 ( id INT, KEY id_index (id) COMMENT ’MERGE_THRESHOLD=40’ ); 

•Setting MERGE_THRESHOLD  for an individual index using ALTER TABLE : 

CREATE TABLE t1 ( id INT, KEY id_index (id) ); ALTER TABLE t1 DROP KEY id_index; ALTER TABLE t1 ADD KEY id_index (id) COMMENT ’MERGE_THRESHOLD=40’; 

•Setting MERGE_THRESHOLD  for an individual index using CREATE INDEX : 

CREATE TABLE t1 (id INT); CREATE INDEX id_index ON t1 (id) COMMENT ’MERGE_THRESHOLD=40’; 

<img src='content_image/861182.jpg'>

## Note 

You cannot modify the MERGE_THRESHOLD  value at the index level for GEN_CLUST_INDEX , which is the clustered index created by InnoDB  when an InnoDB  table is created without a primary key or unique key index. You can only modify the MERGE_THRESHOLD  value for GEN_CLUST_INDEX  by setting MERGE_THRESHOLD  for the table. 

The current MERGE_THRESHOLD  value for an index can be obtained by querying the INNODB_INDEXES table. For example: 

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEXES WHERE NAME=’id_index’ \G *************************** 1. row *************************** INDEX_ID: 91 NAME: id_index TABLE_ID: 68 TYPE: 0 N_FIELDS: 1 PAGE_NO: 4 SPACE: 57 MERGE_THRESHOLD: 40 

You can use SHOW CREATE TABLE  to view the MERGE_THRESHOLD  value for a table, if explicitly defined using the table_option COMMENT  clause: 

mysql> SHOW CREATE TABLE t2 \G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE ‘t2‘ ( ‘id‘ int(11) DEFAULT NULL, KEY ‘id_index‘ (‘id‘) COMMENT ’MERGE_THRESHOLD=40’ 

