15.6.12 Configuring the Merge Threshold for Index Pages

You can configure the MERGE_THRESHOLD value for
index pages. If the “page-full” percentage for an
index page falls below the MERGE_THRESHOLD
value when a row is deleted or when a row is shortened by an
UPDATE operation,
InnoDB attempts to merge the index page with a
neighboring index page. The default
MERGE_THRESHOLD value is 50, which is the
previously hardcoded value. The minimum
MERGE_THRESHOLD value is 1 and the maximum
value is 50.

When the “page-full” percentage for an index page
falls below 50%, which is the default
MERGE_THRESHOLD setting,
InnoDB attempts to merge the index page with a
neighboring page. If both pages are close to 50% full, a page
split can occur soon after the pages are merged. If this
merge-split behavior occurs frequently, it can have an adverse
affect on performance. To avoid frequent merge-splits, you can
lower the MERGE_THRESHOLD value so that
InnoDB attempts page merges at a lower
“page-full” percentage. Merging pages at a lower
page-full percentage leaves more room in index pages and helps
reduce merge-split behavior.

The MERGE_THRESHOLD for index pages can be
defined for a table or for individual indexes. A
MERGE_THRESHOLD value defined for an individual
index takes priority over a MERGE_THRESHOLD
value defined for the table. If undefined, the
MERGE_THRESHOLD value defaults to 50.

Setting MERGE_THRESHOLD for a Table

You can set the MERGE_THRESHOLD value for a
table using the table_optionCOMMENT clause of the
CREATE TABLE statement. For
example:

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.

Querying the MERGE_THRESHOLD Value for an Index

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

A MERGE_THRESHOLD value defined at the index
level takes priority over a MERGE_THRESHOLD
value defined for the table. If undefined,
MERGE_THRESHOLD defaults to 50%
(MERGE_THRESHOLD=50, which is the previously
hardcoded value.

Likewise, you can use SHOW INDEX to
view the MERGE_THRESHOLD value for an index, if
explicitly defined using the
index_optionCOMMENT
clause: