php create index mysql

Работа с индексами в MySQL и MariaDB

Индексы позволяют оптимизировать работу с базой — данные сортируются и СУБД не приходится искать значения среди всех записей. На практике, это может сократить время обработки запросов на несколько порядков.

Данная инструкция применима по отношению как к MySQL, так и MariaDB. В примерах будет использоваться таблица с названием table_test и индекс с названием index_test.

Для больших таблиц не создавайте индексы при помощи phpMyAdmin или других веб-инструментов, так как по умолчанию, интерпретатор PHP настроен на ограниченное время обработки скрипта, а процесс создания индекса может затянуться. Это может привести к потери данных.

Прежде чем начать, правильным будет создадать резервную копию базы.

Командная оболочка

Заходим в командную оболочку MySQL или MariaDB:

* под учетной записью root с запросом пароля.

Выбираем базу, с которой будем работать:

* выбрана база с именем base1

Посмотреть индексы

Прежде чем начать, стоит убедиться в отсутствии индексов, чтобы не создать дубли (инструкция if not exists не работает для create index).

Увидеть имеющиеся индексы:

SHOW INDEX FROM table_test;

Простой индекс

Применяется для ускорения выборки данных.

Вводим следующую команду для создания простого индекса:

mysql> CREATE INDEX index_test ON table_test (name);

* в данном примере создан простой индекс, ускоряющий выборку данных, с именем index_test для поля name таблицы table_test

mysql> DROP INDEX index_test ON table_test;

Полнотекстовый поиск

Необходим для поиска данных с помощью функции MATCH. Индекс создается для таблиц типа «MyISAM» и полей с типом «text».

mysql> CREATE FULLTEXT INDEX index_test ON table_test (name);

mysql> DROP INDEX index_test ON table_test;

Уникальный индекс

Обеспечит уникальность данных для проиндексированного поля.

mysql> CREATE UNIQUE INDEX index_test ON table_test (name);

mysql> DROP INDEX index_test ON table_test;

phpMyAdmin

Способ более удобный, но, как сказано выше, его не стоит применять в продуктивной среде.

В правой части кликаем по необходимому индексу для его создания:

Для удаление в том же разделе Структура кликаем по Индексы и в раскрывшемся списке удаляем индекс:

Источник

Индексирование таблиц MySQL: создание индекса MySQL, удалить индекс и другие операции

От правильно составленной структуры базы данных очень сильно зависит скорость работы всего проекта. Еще одним инструментом, позволяющим значительно сократить время отклика базы, являются индексы БД MySQL. Перед тем, как рассматривать различные операции с ними, стоит определиться, что же такое индексы СУБД и какие преимущества можно получить при их использовании.

Индексы часто используются на высоконагруженных проектах и если Ваш сайт вырос из виртуального хостинга, то потребуется аренда VPS сервера.

Принцип работы индексов очень прост. Для примера рассмотрим запрос:
SELECT Name FROM Persons WHERE Points ON ( [(length)]. )
где:

[UNIQUE | FULLTEXT] – определяет, будет ли индекс содержать только уникальные значения (UNIQUE), или в нем будут присутствовать и повторяющиеся значения (FULLTEXT). По умолчанию используется режим FULLTEXT. Length – определяет длину символов поля для индексирования. Если Length оставить пустым, то в индекс попадет поле целиком вне зависимости от длины.

— уникальный идентификатор индекса. Если это поле не определено, ему будет присвоено имя первого подлежащего индексации столбца.

В версиях MySQL младше 3.22 эта команда не активна, а в более поздних – в плане создания индексов работает аналогично команде ALTER TABLE. При работе с ALTER TABLE добавление записей происходит при помощи команды ADD INDEX, MySQL при помощи этой команды позволяет создавать индексы PRIMARY KEY (создать индекс такого типа при помощи CREATE INDEX нельзя).

Удаление индекса в MySQL

В MySQL удалить индекс можно при помощи такого оператора:
DROP INDEX ON

Источник

Php create index mysql

CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See Section 13.1.8, “ALTER TABLE Statement”. CREATE INDEX cannot be used to create a PRIMARY KEY ; use ALTER TABLE instead. For more information about indexes, see Section 8.3.1, “How MySQL Uses Indexes”.

InnoDB supports secondary indexes on virtual columns. For more information, see Section 13.1.18.8, “Secondary Indexes and Generated Columns”.

When the innodb_stats_persistent setting is enabled, run the ANALYZE TABLE statement for an InnoDB table after creating an index on that table.

The following sections describe different aspects of the CREATE INDEX statement:

Column Prefix Key Parts

For string columns, indexes can be created that use only the leading part of column values, using col_name ( length ) syntax to specify an index prefix length:

Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 767 bytes long for InnoDB tables or 3072 bytes if the innodb_large_prefix option is enabled. For MyISAM tables, the prefix length limit is 1000 bytes. The NDB storage engine does not support prefixes (see Section 21.2.7.6, “Unsupported or Missing Features in NDB Cluster”).

As of MySQL 5.7.17, if a specified index prefix exceeds the maximum column data type size, CREATE INDEX handles the index as follows:

For a nonunique index, either an error occurs (if strict SQL mode is enabled), or the index length is reduced to lie within the maximum column data type size and a warning is produced (if strict SQL mode is not enabled).

For a unique index, an error occurs regardless of SQL mode because reducing the index length might enable insertion of nonunique entries that do not meet the specified uniqueness requirement.

The statement shown here creates an index using the first 10 characters of the name column (assuming that name has a nonbinary string type):

If names in the column usually differ in the first 10 characters, lookups performed using this index should not be much slower than using an index created from the entire name column. Also, using column prefixes for indexes can make the index file much smaller, which could save a lot of disk space and might also speed up INSERT operations.

Unique Indexes

If a table has a PRIMARY KEY or UNIQUE NOT NULL index that consists of a single column that has an integer type, you can use _rowid to refer to the indexed column in SELECT statements, as follows:

_rowid refers to the PRIMARY KEY column if there is a PRIMARY KEY consisting of a single integer column. If there is a PRIMARY KEY but it does not consist of a single integer column, _rowid cannot be used.

Otherwise, _rowid refers to the column in the first UNIQUE NOT NULL index if that index consists of a single integer column. If the first UNIQUE NOT NULL index does not consist of a single integer column, _rowid cannot be used.

Full-Text Indexes

Spatial Indexes

Spatial indexes on spatial columns (created using SPATIAL INDEX ) have these characteristics:

Available only for MyISAM and InnoDB tables. Specifying SPATIAL INDEX for other storage engines results in an error.

Column prefix lengths are prohibited. The full width of each column is indexed.

Columns can be NULL unless the index is a primary key.

For each spatial column in a non- SPATIAL index except POINT columns, a column prefix length must be specified. (This is the same requirement as for indexed BLOB columns.) The prefix length is given in bytes.

The index type for a non- SPATIAL index depends on the storage engine. Currently, B-tree is used.

Index Options

Following the key part list, index options can be given. An index_option value can be any of the following:

For MyISAM tables, KEY_BLOCK_SIZE optionally specifies the size in bytes to use for index key blocks. The value is treated as a hint; a different size could be used if necessary. A KEY_BLOCK_SIZE value specified for an individual index definition overrides a table-level KEY_BLOCK_SIZE value.

KEY_BLOCK_SIZE is not supported at the index level for InnoDB tables. See Section 13.1.18, “CREATE TABLE Statement”.

Some storage engines permit you to specify an index type when creating an index. For example:

Table 13.1, “Index Types Per Storage Engine” shows the permissible index type values supported by different storage engines. Where multiple index types are listed, the first one is the default when no index type specifier is given. Storage engines not listed in the table do not support an index_type clause in index definitions.

Table 13.1 Index Types Per Storage Engine

The index_type clause cannot be used for FULLTEXT INDEX or SPATIAL INDEX specifications. Full-text index implementation is storage engine dependent. Spatial indexes are implemented as R-tree indexes.

BTREE indexes are implemented by the NDB storage engine as T-tree indexes.

For indexes on NDB table columns, the USING option can be specified only for a unique index or primary key. USING HASH prevents the creation of an ordered index; otherwise, creating a unique index or primary key on an NDB table automatically results in the creation of both an ordered index and a hash index, each of which indexes the same set of columns.

For unique indexes that include one or more NULL columns of an NDB table, the hash index can be used only to look up literal values, which means that IS [NOT] NULL conditions require a full scan of the table. One workaround is to make sure that a unique index using one or more NULL columns on such a table is always created in such a way that it includes the ordered index; that is, avoid employing USING HASH when creating the index.

If you specify an index type that is not valid for a given storage engine, but another index type is available that the engine can use without affecting query results, the engine uses the available type. The parser recognizes RTREE as a type name, but currently this cannot be specified for any storage engine.

Use of the index_type option before the ON tbl_name clause is deprecated; you should expect support for use of the option in this position to be removed in a future MySQL release. If an index_type option is given in both the earlier and later positions, the final option applies.

The following tables show index characteristics for the storage engines that support the index_type option.

Table 13.2 InnoDB Storage Engine Index Characteristics

Table 13.3 MyISAM Storage Engine Index Characteristics

Table 13.4 MEMORY Storage Engine Index Characteristics

Table 13.5 NDB Storage Engine Index Characteristics

1. If USING HASH is specified that prevents creation of an implicit ordered index.

WITH PARSER parser_name

Index definitions can include an optional comment of up to 1024 characters.

The MERGE_THRESHOLD for index pages can be configured for individual indexes using the index_option COMMENT clause of the CREATE INDEX statement. For example:

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.

MERGE_THRESHOLD can also be defined at the index level and table level using CREATE TABLE and ALTER TABLE statements. For more information, see Section 14.8.12, “Configuring the Merge Threshold for Index Pages”.

Table Copying and Locking Options

ALGORITHM and LOCK clauses may be given to influence the table copying method and level of concurrency for reading and writing the table while its indexes are being modified. They have the same meaning as for the ALTER TABLE statement. For more information, see Section 13.1.8, “ALTER TABLE Statement”

NDB Cluster formerly supported online CREATE INDEX operations using an alternative syntax that is no longer supported. NDB Cluster now supports online operations using the same ALGORITHM=INPLACE syntax used with the standard MySQL Server. See Section 21.6.11, “Online Operations with ALTER TABLE in NDB Cluster”, for more information.

Источник

Php create index mysql

CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See Section 13.1.9, “ALTER TABLE Statement”. CREATE INDEX cannot be used to create a PRIMARY KEY ; use ALTER TABLE instead. For more information about indexes, see Section 8.3.1, “How MySQL Uses Indexes”.

InnoDB supports secondary indexes on virtual columns. For more information, see Section 13.1.20.9, “Secondary Indexes and Generated Columns”.

When the innodb_stats_persistent setting is enabled, run the ANALYZE TABLE statement for an InnoDB table after creating an index on that table.

Beginning with MySQL 8.0.17, the expr for a key_part specification can take the form (CAST json_expression AS type ARRAY) to create a multi-valued index on a JSON column. See Multi-Valued Indexes.

A key_part specification can end with ASC or DESC to specify whether index values are stored in ascending or descending order. The default is ascending if no order specifier is given. ASC and DESC are not permitted for HASH indexes. ASC and DESC are also not supported for multi-valued indexes. As of MySQL 8.0.12, ASC and DESC are not permitted for SPATIAL indexes.

The following sections describe different aspects of the CREATE INDEX statement:

Column Prefix Key Parts

For string columns, indexes can be created that use only the leading part of column values, using col_name ( length ) syntax to specify an index prefix length:

Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 767 bytes long for InnoDB tables that use the REDUNDANT or COMPACT row format. The prefix length limit is 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format. For MyISAM tables, the prefix length limit is 1000 bytes. The NDB storage engine does not support prefixes (see Section 23.2.7.6, “Unsupported or Missing Features in NDB Cluster”).

If a specified index prefix exceeds the maximum column data type size, CREATE INDEX handles the index as follows:

For a nonunique index, either an error occurs (if strict SQL mode is enabled), or the index length is reduced to lie within the maximum column data type size and a warning is produced (if strict SQL mode is not enabled).

For a unique index, an error occurs regardless of SQL mode because reducing the index length might enable insertion of nonunique entries that do not meet the specified uniqueness requirement.

The statement shown here creates an index using the first 10 characters of the name column (assuming that name has a nonbinary string type):

If names in the column usually differ in the first 10 characters, lookups performed using this index should not be much slower than using an index created from the entire name column. Also, using column prefixes for indexes can make the index file much smaller, which could save a lot of disk space and might also speed up INSERT operations.

Functional Key Parts

A “ normal ” index indexes column values or prefixes of column values. For example, in the following table, the index entry for a given t1 row includes the full col1 value and a prefix of the col2 value consisting of its first 10 characters:

MySQL 8.0.13 and higher supports functional key parts that index expression values rather than column or column prefix values. Use of functional key parts enables indexing of values not stored directly in the table. Examples:

An index with multiple key parts can mix nonfunctional and functional key parts.

ASC and DESC are supported for functional key parts.

Functional key parts must adhere to the following rules. An error occurs if a key part definition contains disallowed constructs.

In index definitions, enclose expressions within parentheses to distinguish them from columns or column prefixes. For example, this is permitted; the expressions are enclosed within parentheses:

This produces an error; the expressions are not enclosed within parentheses:

A functional key part cannot consist solely of a column name. For example, this is not permitted:

Instead, write the key parts as nonfunctional key parts, without parentheses:

A functional key part expression cannot refer to column prefixes. For a workaround, see the discussion of SUBSTRING() and CAST() later in this section.

Functional key parts are not permitted in foreign key specifications.

Functional indexes are implemented as hidden virtual generated columns, which has these implications:

Each functional key part counts against the limit on total number of table columns; see Section 8.4.7, “Limits on Table Column Count and Row Size”.

Functional key parts inherit all restrictions that apply to generated columns. Examples:

Only functions permitted for generated columns are permitted for functional key parts.

Subqueries, parameters, variables, stored functions, and loadable functions are not permitted.

The virtual generated column itself requires no storage. The index itself takes up storage space as any other index.

UNIQUE is supported for indexes that include functional key parts. However, primary keys cannot include functional key parts. A primary key requires the generated column to be stored, but functional key parts are implemented as virtual generated columns, not stored generated columns.

SPATIAL and FULLTEXT indexes cannot have functional key parts.

If a table contains no primary key, InnoDB automatically promotes the first UNIQUE NOT NULL index to the primary key. This is not supported for UNIQUE NOT NULL indexes that have functional key parts.

Nonfunctional indexes raise a warning if there are duplicate indexes. Indexes that contain functional key parts do not have this feature.

To remove a column that is referenced by a functional key part, the index must be removed first. Otherwise, an error occurs.

Functional key parts enable indexing of values that cannot be indexed otherwise, such as JSON values. However, this must be done correctly to achieve the desired effect. For example, this syntax does not work:

The syntax fails because:

MySQL cannot index LONGTEXT columns specified without a prefix length on the key part, and prefix lengths are not permitted in functional key parts.

To index the JSON column, you could try using the CAST() function as follows:

The hidden generated column is assigned the VARCHAR(30) data type, which can be indexed. But this approach produces a new issue when trying to use the index:

CAST() returns a string with the collation utf8mb4_0900_ai_ci (the server default collation).

JSON_UNQUOTE() returns a string with the collation utf8mb4_bin (hard coded).

As a result, there is a collation mismatch between the indexed expression in the preceding table definition and the WHERE clause expression in the following query:

The index is not used because the expressions in the query and the index differ. To support this kind of scenario for functional key parts, the optimizer automatically strips CAST() when looking for an index to use, but only if the collation of the indexed expression matches that of the query expression. For an index with a functional key part to be used, either of the following two solutions work (although they differ somewhat in effect):

Solution 1. Assign the indexed expression the same collation as JSON_UNQUOTE() :

Solution 2. Specify the full expression in the query:

Be aware that although the optimizer supports automatically stripping CAST() with indexed generated columns, the following approach does not work because it produces a different result with and without an index (Bug#27337092):

Unique Indexes

If a table has a PRIMARY KEY or UNIQUE NOT NULL index that consists of a single column that has an integer type, you can use _rowid to refer to the indexed column in SELECT statements, as follows:

_rowid refers to the PRIMARY KEY column if there is a PRIMARY KEY consisting of a single integer column. If there is a PRIMARY KEY but it does not consist of a single integer column, _rowid cannot be used.

Otherwise, _rowid refers to the column in the first UNIQUE NOT NULL index if that index consists of a single integer column. If the first UNIQUE NOT NULL index does not consist of a single integer column, _rowid cannot be used.

Full-Text Indexes

Multi-Valued Indexes

As of MySQL 8.0.17, InnoDB supports multi-valued indexes. A multi-valued index is a secondary index defined on a column that stores an array of values. A “ normal ” index has one index record for each data record (1:1). A multi-valued index can have multiple index records for a single data record (N:1). Multi-valued indexes are intended for indexing JSON arrays. For example, a multi-valued index defined on the array of zip codes in the following JSON document creates an index record for each zip code, with each index record referencing the same data record.

Creating multi-valued Indexes

CREATE TABLE plus ALTER TABLE :

CREATE TABLE plus CREATE INDEX :

A multi-valued index can also be defined as part of a composite index. This example shows a composite index that includes two single-valued parts (for the id and modified columns), and one multi-valued part (for the custinfo column):

Only one multi-valued key part can be used in a composite index. The multi-valued key part may be used in any order relative to the other parts of the key. In other words, the ALTER TABLE statement just shown could have used comp(id, (CAST(custinfo->’$.zipcode’ AS UNSIGNED ARRAY), modified)) (or any other ordering) and still have been valid.

Using multi-valued Indexes

The optimizer uses a multi-valued index to fetch records when the following functions are specified in a WHERE clause:

We can demonstrate this by creating and populating the customers table using the following CREATE TABLE and INSERT statements:

Next, we run EXPLAIN on each of the previous three queries:

None of the three queries just shown are able to use any keys. To solve this problem, we can add a multi-valued index on the zipcode array in the JSON column ( custinfo ), like this:

When we run the previous EXPLAIN statements again, we can now observe that the queries can (and do) use the index zips that was just created:

A multi-valued index can be defined as a unique key. If defined as a unique key, attempting to insert a value already present in the multi-valued index returns a duplicate key error. If duplicate values are already present, attempting to add a unique multi-valued index fails, as shown here:

Characteristics of Multi-Valued Indexes

Multi-valued indexes have the additional characteristics listed here:

DML operations that affect multi-valued indexes are handled in the same way as DML operations that affect a normal index, with the only difference being that there may be more than one insert or update for a single clustered index record.

Nullability and multi-valued indexes:

If multi-valued key part has an empty array, no entries are added to the index, and the data record is not accessible by an index scan.

Because multi-valued indexes are virtual indexes on virtual columns, they must adhere to the same rules as secondary indexes on virtual generated columns.

Index records are not added for empty arrays.

Limitations and Restrictions on Multi-valued Indexes

Multi-valued indexes are subject to the limitations and restrictions listed here:

In this case, all values matching the JSON expression are stored in the index as a single flat array.

An index with a multi-valued key part does not support ordering and therefore cannot be used as a primary key. For the same reason, a multi-valued index cannot be defined using the ASC or DESC keyword.

A multi-valued index cannot be a covering index.

The maximum number of values per record for a multi-valued index is determined by the amount of data than can be stored on a single undo log page, which is 65221 bytes (64K minus 315 bytes for overhead), which means that the maximum total length of key values is also 65221 bytes. The maximum number of keys depends on various factors, which prevents defining a specific limit. Tests have shown a multi-valued index to permit as many as 1604 integer keys per record, for example. When the limit is reached, an error similar to the following is reported: ERROR 3905 (HY000): Exceeded max number of values per record for multi-valued index ‘idx’ by 1 value(s).

The only type of expression that is permitted in a multi-valued key part is a JSON expression. The expression need not reference an existing element in a JSON document inserted into the indexed column, but must itself be syntactically valid.

Because index records for the same clustered index record are dispersed throughout a multi-valued index, a multi-valued index does not support range scans or index-only scans.

Multi-valued indexes are not permitted in foreign key specifications.

Index prefixes cannot be defined for multi-valued indexes.

Multi-valued indexes cannot be defined on data cast as BINARY (see the description of the CAST() function).

Character sets and collations other than the following two combinations of character set and collation are not supported for multi-valued indexes:

The binary character set with the default binary collation

The utf8mb4 character set with the default utf8mb4_0900_as_cs collation.

Spatial Indexes

Spatial indexes on spatial columns have these characteristics:

Available only for InnoDB and MyISAM tables. Specifying SPATIAL INDEX for other storage engines results in an error.

As of MySQL 8.0.12, an index on a spatial column must be a SPATIAL index. The SPATIAL keyword is thus optional but implicit for creating an index on a spatial column.

Available for single spatial columns only. A spatial index cannot be created over multiple spatial columns.

Column prefix lengths are prohibited. The full width of each column is indexed.

Not permitted for a primary key or unique index.

Columns can be NULL unless the index is a primary key.

The index type for a non- SPATIAL index depends on the storage engine. Currently, B-tree is used.

Index Options

Following the key part list, index options can be given. An index_option value can be any of the following:

For MyISAM tables, KEY_BLOCK_SIZE optionally specifies the size in bytes to use for index key blocks. The value is treated as a hint; a different size could be used if necessary. A KEY_BLOCK_SIZE value specified for an individual index definition overrides a table-level KEY_BLOCK_SIZE value.

KEY_BLOCK_SIZE is not supported at the index level for InnoDB tables. See Section 13.1.20, “CREATE TABLE Statement”.

Some storage engines permit you to specify an index type when creating an index. For example:

Table 13.1, “Index Types Per Storage Engine” shows the permissible index type values supported by different storage engines. Where multiple index types are listed, the first one is the default when no index type specifier is given. Storage engines not listed in the table do not support an index_type clause in index definitions.

Table 13.1 Index Types Per Storage Engine

The index_type clause cannot be used for FULLTEXT INDEX or (prior to MySQL 8.0.12) SPATIAL INDEX specifications. Full-text index implementation is storage engine dependent. Spatial indexes are implemented as R-tree indexes.

If you specify an index type that is not valid for a given storage engine, but another index type is available that the engine can use without affecting query results, the engine uses the available type. The parser recognizes RTREE as a type name. As of MySQL 8.0.12, this is permitted only for SPATIAL indexes. Prior to 8.0.12, RTREE cannot be specified for any storage engine.

BTREE indexes are implemented by the NDB storage engine as T-tree indexes.

For indexes on NDB table columns, the USING option can be specified only for a unique index or primary key. USING HASH prevents the creation of an ordered index; otherwise, creating a unique index or primary key on an NDB table automatically results in the creation of both an ordered index and a hash index, each of which indexes the same set of columns.

For unique indexes that include one or more NULL columns of an NDB table, the hash index can be used only to look up literal values, which means that IS [NOT] NULL conditions require a full scan of the table. One workaround is to make sure that a unique index using one or more NULL columns on such a table is always created in such a way that it includes the ordered index; that is, avoid employing USING HASH when creating the index.

If you specify an index type that is not valid for a given storage engine, but another index type is available that the engine can use without affecting query results, the engine uses the available type. The parser recognizes RTREE as a type name, but currently this cannot be specified for any storage engine.

Use of the index_type option before the ON tbl_name clause is deprecated; expect support for use of the option in this position to be removed in a future MySQL release. If an index_type option is given in both the earlier and later positions, the final option applies.

The following tables show index characteristics for the storage engines that support the index_type option.

Table 13.2 InnoDB Storage Engine Index Characteristics

Table 13.3 MyISAM Storage Engine Index Characteristics

Table 13.4 MEMORY Storage Engine Index Characteristics

Table 13.5 NDB Storage Engine Index Characteristics

1. USING HASH prevents creation of an implicit ordered index.

WITH PARSER parser_name

Index definitions can include an optional comment of up to 1024 characters.

The MERGE_THRESHOLD for index pages can be configured for individual indexes using the index_option COMMENT clause of the CREATE INDEX statement. For example:

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.

MERGE_THRESHOLD can also be defined at the index level and table level using CREATE TABLE and ALTER TABLE statements. For more information, see Section 15.8.11, “Configuring the Merge Threshold for Index Pages”.

Specify index visibility. Indexes are visible by default. An invisible index is not used by the optimizer. Specification of index visibility applies to indexes other than primary keys (either explicit or implicit). For more information, see Section 8.3.12, “Invisible Indexes”.

ENGINE_ATTRIBUTE and SECONDARY_ENGINE_ATTRIBUTE options (available as of MySQL 8.0.21) are used to specify index attributes for primary and secondary storage engines. The options are reserved for future use.

Permitted values are a string literal containing a valid JSON document or an empty string (»). Invalid JSON is rejected.

ENGINE_ATTRIBUTE and SECONDARY_ENGINE_ATTRIBUTE values can be repeated without error. In this case, the last specified value is used.

ENGINE_ATTRIBUTE and SECONDARY_ENGINE_ATTRIBUTE values are not checked by the server, nor are they cleared when the table’s storage engine is changed.

Table Copying and Locking Options

ALGORITHM and LOCK clauses may be given to influence the table copying method and level of concurrency for reading and writing the table while its indexes are being modified. They have the same meaning as for the ALTER TABLE statement. For more information, see Section 13.1.9, “ALTER TABLE Statement”

NDB Cluster supports online operations using the same ALGORITHM=INPLACE syntax used with the standard MySQL Server. See Section 23.6.11, “Online Operations with ALTER TABLE in NDB Cluster”, for more information.

Источник