`ALTER MODIFY INDEX` command

[cangyin]

Use case

Conveniently modify existing index expression or granularity.

There're ADD INDEX and DROP INDEX to achieve the target. But DROP INDEX requires data mutation.

Describe the solution you'd like

ALTER <table> MODIFY INDEX <index_name> <index declaration> [VERSION <an_auto_increase_version_number>]

For example if we do

ALTER table MODIFY INDEX `idx_a` col_a TYPE bloom_filter GRANULARITY 1

It is equivalent to

ALTER table DROP INDEX `idx_a`
ALTER table  ADD INDEX `idx_a_1` col_a TYPE bloom_filter GRANULARITY 1

only without the data mutation from DROP INDEX

Again if we do

ALTER table MODIFY INDEX `idx_a` col_a TYPE bloom_filter GRANULARITY 4

This time it is equivalent to

ALTER table DROP INDEX `idx_a_1`
ALTER table  ADD INDEX `idx_a_2` col_a TYPE bloom_filter GRANULARITY 4

the index name remains unchanged as idx_a after ALTER MODIFY INDEX.

[cangyin]

Or a setting like clear_index_after_drop for DROP INDEX command to avoid data mutation.