[MDEV-7317] Make an index ignorable to the optimizer - Jira

James Briggs created issue - 2014-12-14 07:13

James Briggs made changes - 2014-12-14 07:15

Field	Original Value	New Value
Description	Oracle 11g added invisible indexes, which would also be useful in MariaDB. Morgan Tocker filed a MySQL bug to add that feature: http://bugs.mysql.com/bug.php?id=70299 http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_5011.htm "VISIBLE \| INVISIBLE Use this clause to specify whether the index is visible or invisible to the optimizer. An invisible index is maintained by DML operations, but it is not be used by the optimizer during queries unless you explicitly set the parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE at the session or system level. To determine whether an existing index is visible or invisible to the optimizer, you can query the VISIBILITY column of the USER_, DBA_, ALL_INDEXES data dictionary views." http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#ADMIN12317 http://www.dba-oracle.com/t_11g_new_index_features.htm Note that MariaDB does have an older, more general mechanism already for making invisible indexes: ALTER TABLE t1 DISABLE KEYS; ALTER TABLE t1 ENABLE KEYS; Suggested new syntax in MariaDB: CREATE TABLE t1 (id int(10), name char(20), KEY idx_name(name) INVISIBLE); ALTER TABLE t1 CHANGE INDEX idx_name INVISIBLE; ALTER TABLE t1 CHANGE INDEX idx_name VISIBLE; CREATE INDEX ... index_option: KEY_BLOCK_SIZE [=] value \| index_type \| WITH PARSER parser_name \| INVISIBLE \| VISIBLE \| COMMENT 'string' Some of the server areas this code will affect: - parser (create table, create index, alter table) - optimizer	Oracle 11g added invisible indexes, which would also be useful in MariaDB. Morgan Tocker filed a MySQL bug to add that feature: http://bugs.mysql.com/bug.php?id=70299 http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_5011.htm "VISIBLE \| INVISIBLE Use this clause to specify whether the index is visible or invisible to the optimizer. An invisible index is maintained by DML operations, but it is not be used by the optimizer during queries unless you explicitly set the parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE at the session or system level. To determine whether an existing index is visible or invisible to the optimizer, you can query the VISIBILITY column of the USER_, DBA_, ALL_INDEXES data dictionary views." http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#ADMIN12317 http://www.dba-oracle.com/t_11g_new_index_features.htm Note that MariaDB does have an older, more general mechanism already for making invisible indexes: ALTER TABLE t1 DISABLE KEYS; ALTER TABLE t1 ENABLE KEYS; Suggested new syntax in MariaDB: CREATE TABLE t1 (id int(10), name char(20), KEY idx_name(name) INVISIBLE); ALTER TABLE t1 CHANGE INDEX idx_name INVISIBLE; ALTER TABLE t1 CHANGE INDEX idx_name VISIBLE; CREATE INDEX ... index_option: KEY_BLOCK_SIZE [=] value / index_type / WITH PARSER parser_name / INVISIBLE \| VISIBLE / COMMENT 'string' Some of the server areas this code will affect: - parser (create table, create index, alter table) - optimizer

Sergei Golubchik made changes - 2014-12-14 10:37

Description

Oracle 11g added invisible indexes, which would also be useful in MariaDB.

Morgan Tocker filed a MySQL bug to add that feature:
http://bugs.mysql.com/bug.php?id=70299

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_5011.htm
"VISIBLE | INVISIBLE Use this clause to specify whether the index is visible or invisible to the optimizer. An invisible index is maintained by DML operations, but it is not be used by the optimizer during queries unless you explicitly set the parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE at the session or system level.

To determine whether an existing index is visible or invisible to the optimizer, you can query the VISIBILITY column of the USER_, DBA_, ALL_INDEXES data dictionary views."

http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#ADMIN12317

http://www.dba-oracle.com/t_11g_new_index_features.htm

Note that MariaDB does have an older, more general mechanism already for making invisible indexes:

ALTER TABLE t1 DISABLE KEYS;
ALTER TABLE t1 ENABLE KEYS;

Suggested new syntax in MariaDB:

CREATE TABLE t1 (id int(10), name char(20), KEY idx_name(name) INVISIBLE);
ALTER TABLE t1 CHANGE INDEX idx_name INVISIBLE;
ALTER TABLE t1 CHANGE INDEX idx_name VISIBLE;
CREATE INDEX ...
index_option:
    KEY_BLOCK_SIZE [=] value
    / index_type
    / WITH PARSER parser_name
    / INVISIBLE | VISIBLE
    / COMMENT 'string'

Some of the server areas this code will affect:

- parser (create table, create index, alter table)
- optimizer

Oracle 11g added invisible indexes, which would also be useful in MariaDB.

Morgan Tocker filed a MySQL bug to add that feature:
http://bugs.mysql.com/bug.php?id=70299

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_5011.htm
"VISIBLE | INVISIBLE Use this clause to specify whether the index is visible or invisible to the optimizer. An invisible index is maintained by DML operations, but it is not be used by the optimizer during queries unless you explicitly set the parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE at the session or system level.

To determine whether an existing index is visible or invisible to the optimizer, you can query the VISIBILITY column of the USER_, DBA_, ALL_INDEXES data dictionary views."

http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#ADMIN12317

http://www.dba-oracle.com/t_11g_new_index_features.htm

Note that MariaDB does have an older, more general mechanism already for making invisible indexes:
{code:sql}
ALTER TABLE t1 DISABLE KEYS;
ALTER TABLE t1 ENABLE KEYS;
{code}
Suggested new syntax in MariaDB:
{noformat}
CREATE TABLE t1 (id int(10), name char(20), KEY idx_name(name) INVISIBLE);
ALTER TABLE t1 CHANGE INDEX idx_name INVISIBLE;
ALTER TABLE t1 CHANGE INDEX idx_name VISIBLE;
CREATE INDEX ...
index_option:
    KEY_BLOCK_SIZE [=] value
    / index_type
    / WITH PARSER parser_name
    / INVISIBLE | VISIBLE
    / COMMENT 'string'
{noformat}
Some of the server areas this code will affect:
- parser (create table, create index, alter table)
- optimizer

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:50

Workflow

MariaDB v2 [ 58951 ]

MariaDB v3 [ 62488 ]

Sergei Golubchik made changes - 2015-10-30 21:34

Summary

Feature request: make an index invisible to the optimizer

Make an index invisible to the optimizer

Michael Widenius made changes - 2020-04-08 22:04

Link

This issue includes ~~MDEV-22199~~ [ ~~MDEV-22199~~ ]

Varun Gupta (Inactive) made changes - 2020-04-28 08:08

Assignee

Varun Gupta [ varun ]

Varun Gupta (Inactive) made changes - 2020-04-28 11:47

Fix Version/s

10.6 [ 24028 ]

Varun Gupta (Inactive) made changes - 2020-04-28 11:47

Priority

Minor [ 4 ]

Major [ 3 ]

Varun Gupta (Inactive) made changes - 2020-05-04 05:15

Status

Open [ 1 ]

In Progress [ 3 ]

Varun Gupta (Inactive) made changes - 2020-05-04 09:32

Description

Oracle 11g added invisible indexes, which would also be useful in MariaDB.

Morgan Tocker filed a MySQL bug to add that feature:
http://bugs.mysql.com/bug.php?id=70299

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_5011.htm
"VISIBLE | INVISIBLE Use this clause to specify whether the index is visible or invisible to the optimizer. An invisible index is maintained by DML operations, but it is not be used by the optimizer during queries unless you explicitly set the parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE at the session or system level.

To determine whether an existing index is visible or invisible to the optimizer, you can query the VISIBILITY column of the USER_, DBA_, ALL_INDEXES data dictionary views."

http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#ADMIN12317

http://www.dba-oracle.com/t_11g_new_index_features.htm

Note that MariaDB does have an older, more general mechanism already for making invisible indexes:
{code:sql}
ALTER TABLE t1 DISABLE KEYS;
ALTER TABLE t1 ENABLE KEYS;
{code}
Suggested new syntax in MariaDB:
{noformat}
CREATE TABLE t1 (id int(10), name char(20), KEY idx_name(name) INVISIBLE);
ALTER TABLE t1 CHANGE INDEX idx_name INVISIBLE;
ALTER TABLE t1 CHANGE INDEX idx_name VISIBLE;
CREATE INDEX ...
index_option:
    KEY_BLOCK_SIZE [=] value
    / index_type
    / WITH PARSER parser_name
    / INVISIBLE | VISIBLE
    / COMMENT 'string'
{noformat}
Some of the server areas this code will affect:
- parser (create table, create index, alter table)
- optimizer

SYNTAX

An index can be ignored in the following way:

  1) Adding IGNORED to the key specification in CREATE TABLE
    Example
      CREATE TABLE t1(a INT, key key1(a) IGNORE)
  2) Adding IGNORED to the key specification in CREATE INDEX
    Example
      CREATE INDEX key1 on t1 (a) IGNORE
  3) Adding IGNORED to the key in the ALTER statement

     New syntax: ALTER TABLE table_name ALTER INDEX key_name ignorability

  ignorability:
        | IGNORE_SYM
        | NOT IGNORE_SYM

    Example;
       ALTER TABLE t1 ADD INDEX key1 IGNORE;

Specifications

a) Store the information about the ignorability of the index in the FRM
b) A primary index cannot be made IGNORABLE
c) A non-nullable unique key that is promoted to a primary key cannot be made IGNORABLE
d) CHANGES in the output of SHOW CREATE TABLE and SHOW INDEXES|KEYS to show if the index is IGNORED or not
e) Introduce an optimizer switch that would allow the optimizer to not consider the ignorable indexes
    ignore_indexes : on/off [name for the switch can be reconisdered]
f) An ALTER INDEX operation should use the INPLACE algorithm by default.

Varun Gupta (Inactive) made changes - 2020-05-04 09:32

Summary

Make an index invisible to the optimizer

Make an index ignorable to the optimizer

Varun Gupta (Inactive) made changes - 2020-05-04 09:33

Description

SYNTAX

An index can be ignored in the following way:

  1) Adding IGNORED to the key specification in CREATE TABLE
    Example
      CREATE TABLE t1(a INT, key key1(a) IGNORE)
  2) Adding IGNORED to the key specification in CREATE INDEX
    Example
      CREATE INDEX key1 on t1 (a) IGNORE
  3) Adding IGNORED to the key in the ALTER statement

     New syntax: ALTER TABLE table_name ALTER INDEX key_name ignorability

  ignorability:
        | IGNORE_SYM
        | NOT IGNORE_SYM

    Example;
       ALTER TABLE t1 ADD INDEX key1 IGNORE;

Specifications

a) Store the information about the ignorability of the index in the FRM
b) A primary index cannot be made IGNORABLE
c) A non-nullable unique key that is promoted to a primary key cannot be made IGNORABLE
d) CHANGES in the output of SHOW CREATE TABLE and SHOW INDEXES|KEYS to show if the index is IGNORED or not
e) Introduce an optimizer switch that would allow the optimizer to not consider the ignorable indexes
    ignore_indexes : on/off [name for the switch can be reconisdered]
f) An ALTER INDEX operation should use the INPLACE algorithm by default.

Varun Gupta (Inactive) made changes - 2020-05-04 09:34

Description

SYNTAX

An index can be ignored in the following way:

  1) Adding IGNORED to the key specification in CREATE TABLE
    Example
      CREATE TABLE t1(a INT, key key1(a) IGNORE)
  2) Adding IGNORED to the key specification in CREATE INDEX
    Example
      CREATE INDEX key1 on t1 (a) IGNORE
  3) Adding IGNORED to the key in the ALTER statement

     New syntax: ALTER TABLE table_name ALTER INDEX key_name ignorability

  ignorability:
        | IGNORE_SYM
        | NOT IGNORE_SYM

    Example;
       ALTER TABLE t1 ADD INDEX key1 IGNORE;

Specifications

a) Store the information about the ignorability of the index in the FRM
b) A primary index cannot be made IGNORABLE
c) A non-nullable unique key that is promoted to a primary key cannot be made IGNORABLE
d) CHANGES in the output of SHOW CREATE TABLE and SHOW INDEXES|KEYS to show if the index is IGNORED or not
e) Introduce an optimizer switch that would allow the optimizer to not consider the ignorable indexes
    ignore_indexes : on/off [name for the switch can be reconisdered]
f) An ALTER INDEX operation should use the INPLACE algorithm by default.

SYNTAX

An index can be ignored in the following way:

  1) Adding IGNORED to the key specification in CREATE TABLE
    Example
      CREATE TABLE t1(a INT, key key1(a) IGNORE)
  2) Adding IGNORED to the key specification in CREATE INDEX
    Example
      CREATE INDEX key1 on t1 (a) IGNORE
  3) Adding IGNORED to the key in the ALTER statement

     New syntax: ALTER TABLE table_name ALTER INDEX key_name ignorability

  ignorability:
        | IGNORE_SYM
        | NOT IGNORE_SYM

    Example;
       ALTER TABLE t1 ADD INDEX key1 IGNORE;

Specifications

* Store the information about the ignorability of the index in the FRM.
* A primary index cannot be made IGNORABLE.
* A non-nullable unique key that is promoted to a primary key cannot be made IGNORABLE.
* CHANGES in the output of SHOW CREATE TABLE and SHOW INDEXES|KEYS to show if the index is IGNORED or not.
* Introduce an optimizer switch that would allow the optimizer to not consider the ignorable indexes
   ignore_indexes: on/off [name for the switch can be reconisdered]
* An ALTER INDEX operation should use the INPLACE algorithm by default.

Varun Gupta (Inactive) made changes - 2020-05-04 09:36

Description

SYNTAX

An index can be ignored in the following way:

  1) Adding IGNORED to the key specification in CREATE TABLE
    Example
      CREATE TABLE t1(a INT, key key1(a) IGNORE)
  2) Adding IGNORED to the key specification in CREATE INDEX
    Example
      CREATE INDEX key1 on t1 (a) IGNORE
  3) Adding IGNORED to the key in the ALTER statement

     New syntax: ALTER TABLE table_name ALTER INDEX key_name ignorability

  ignorability:
        | IGNORE_SYM
        | NOT IGNORE_SYM

    Example;
       ALTER TABLE t1 ADD INDEX key1 IGNORE;

Specifications

* Store the information about the ignorability of the index in the FRM.
* A primary index cannot be made IGNORABLE.
* A non-nullable unique key that is promoted to a primary key cannot be made IGNORABLE.
* CHANGES in the output of SHOW CREATE TABLE and SHOW INDEXES|KEYS to show if the index is IGNORED or not.
* Introduce an optimizer switch that would allow the optimizer to not consider the ignorable indexes
   ignore_indexes: on/off [name for the switch can be reconisdered]
* An ALTER INDEX operation should use the INPLACE algorithm by default.

h3. SYNTAX
h3.
An index can be ignored in the following way:

  1) Adding IGNORED to the key specification in CREATE TABLE
      Example

{code:sql}
CREATE TABLE t1(a INT, key key1(a) IGNORE)
{code}

  2) Adding IGNORED to the key specification in CREATE INDEX
       Example
       {code:sql}
         CREATE INDEX key1 on t1 (a) IGNORE
      {code}
  3) Adding IGNORED to the key in the ALTER statement

     New syntax: ALTER TABLE table_name ALTER INDEX key_name ignorability

  ignorability:
        | IGNORE_SYM
        | NOT IGNORE_SYM

    Example:
{code:sql}
ALTER TABLE t1 ADD INDEX key1 IGNORE;
{code}

h3. Specifications

* Store the information about the ignorability of the index in the FRM.
* A primary index cannot be made IGNORABLE.
* A non-nullable unique key that is promoted to a primary key cannot be made IGNORABLE.
* CHANGES in the output of SHOW CREATE TABLE and SHOW INDEXES|KEYS to show if the index is IGNORED or not.
* Introduce an optimizer switch that would allow the optimizer to not consider the ignorable indexes
   ignore_indexes: on/off [name for the switch can be reconisdered]
* An ALTER INDEX operation should use the INPLACE algorithm by default.

Varun Gupta (Inactive) made changes - 2020-05-04 09:36

Description

h3. SYNTAX
h3.
An index can be ignored in the following way:

  1) Adding IGNORED to the key specification in CREATE TABLE
      Example

{code:sql}
CREATE TABLE t1(a INT, key key1(a) IGNORE)
{code}

  2) Adding IGNORED to the key specification in CREATE INDEX
       Example
       {code:sql}
         CREATE INDEX key1 on t1 (a) IGNORE
      {code}
  3) Adding IGNORED to the key in the ALTER statement

     New syntax: ALTER TABLE table_name ALTER INDEX key_name ignorability

  ignorability:
        | IGNORE_SYM
        | NOT IGNORE_SYM

    Example:
{code:sql}
ALTER TABLE t1 ADD INDEX key1 IGNORE;
{code}

h3. Specifications

* Store the information about the ignorability of the index in the FRM.
* A primary index cannot be made IGNORABLE.
* A non-nullable unique key that is promoted to a primary key cannot be made IGNORABLE.
* CHANGES in the output of SHOW CREATE TABLE and SHOW INDEXES|KEYS to show if the index is IGNORED or not.
* Introduce an optimizer switch that would allow the optimizer to not consider the ignorable indexes
   ignore_indexes: on/off [name for the switch can be reconisdered]
* An ALTER INDEX operation should use the INPLACE algorithm by default.

h3. SYNTAX

An index can be ignored in the following way:

  1) Adding IGNORED to the key specification in CREATE TABLE
      Example

{code:sql}
CREATE TABLE t1(a INT, key key1(a) IGNORE);
{code}

  2) Adding IGNORED to the key specification in CREATE INDEX
       Example
       {code:sql}
         CREATE INDEX key1 on t1 (a) IGNORE;
      {code}
  3) Adding IGNORED to the key in the ALTER statement

     New syntax: ALTER TABLE table_name ALTER INDEX key_name ignorability

  ignorability:
        | IGNORE_SYM
        | NOT IGNORE_SYM

    Example:
{code:sql}
ALTER TABLE t1 ADD INDEX key1 IGNORE;
{code}

h3. Specifications

* Store the information about the ignorability of the index in the FRM.
* A primary index cannot be made IGNORABLE.
* A non-nullable unique key that is promoted to a primary key cannot be made IGNORABLE.
* CHANGES in the output of SHOW CREATE TABLE and SHOW INDEXES|KEYS to show if the index is IGNORED or not.
* Introduce an optimizer switch that would allow the optimizer to not consider the ignorable indexes
   ignore_indexes: on/off [name for the switch can be reconisdered]
* An ALTER INDEX operation should use the INPLACE algorithm by default.

Varun Gupta (Inactive) made changes - 2020-05-04 09:37

Description

h3. SYNTAX

An index can be ignored in the following way:

  1) Adding IGNORED to the key specification in CREATE TABLE
      Example

{code:sql}
CREATE TABLE t1(a INT, key key1(a) IGNORE);
{code}

  2) Adding IGNORED to the key specification in CREATE INDEX
       Example
       {code:sql}
         CREATE INDEX key1 on t1 (a) IGNORE;
      {code}
  3) Adding IGNORED to the key in the ALTER statement

     New syntax: ALTER TABLE table_name ALTER INDEX key_name ignorability

  ignorability:
        | IGNORE_SYM
        | NOT IGNORE_SYM

    Example:
{code:sql}
ALTER TABLE t1 ADD INDEX key1 IGNORE;
{code}

h3. Specifications

* Store the information about the ignorability of the index in the FRM.
* A primary index cannot be made IGNORABLE.
* A non-nullable unique key that is promoted to a primary key cannot be made IGNORABLE.
* CHANGES in the output of SHOW CREATE TABLE and SHOW INDEXES|KEYS to show if the index is IGNORED or not.
* Introduce an optimizer switch that would allow the optimizer to not consider the ignorable indexes
   ignore_indexes: on/off [name for the switch can be reconisdered]
* An ALTER INDEX operation should use the INPLACE algorithm by default.

h3. SYNTAX

An index can be ignored in the following way:

  1) Adding IGNORED to the key specification in CREATE TABLE
      Example

{code:sql}
CREATE TABLE t1(a INT, key key1(a) IGNORE);
{code}

  2) Adding IGNORED to the key specification in CREATE INDEX
       Example

{code:sql}
CREATE INDEX key1 on t1 (a) IGNORE;
{code}

  3) Adding IGNORED to the key in the ALTER statement

     New syntax: ALTER TABLE table_name ALTER INDEX key_name ignorability

  ignorability:
        | IGNORE_SYM
        | NOT IGNORE_SYM

    Example:
{code:sql}
ALTER TABLE t1 ADD INDEX key1 IGNORE;
{code}

h3. Specifications

* Store the information about the ignorability of the index in the FRM.
* A primary index cannot be made IGNORABLE.
* A non-nullable unique key that is promoted to a primary key cannot be made IGNORABLE.
* CHANGES in the output of SHOW CREATE TABLE and SHOW INDEXES|KEYS to show if the index is IGNORED or not.
* Introduce an optimizer switch that would allow the optimizer to not consider the ignorable indexes
   ignore_indexes: on/off [name for the switch can be reconisdered]
* An ALTER INDEX operation should use the INPLACE algorithm by default.

Varun Gupta (Inactive) made changes - 2020-05-06 12:28

Assignee	Varun Gupta [ varun ]	Sergei Petrunia [ psergey ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Varun Gupta (Inactive) made changes - 2020-05-06 12:28

Assignee

Sergei Petrunia [ psergey ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2020-08-16 19:56

Rank

Ranked higher

Ralf Gebhardt made changes - 2021-02-17 16:00

Priority

Major [ 3 ]

Critical [ 2 ]

Ralf Gebhardt made changes - 2021-02-17 16:00

Assignee

Sergei Golubchik [ serg ]

Sergei Petrunia [ psergey ]

Sergei Petrunia made changes - 2021-02-24 18:15

Assignee	Sergei Petrunia [ psergey ]	Varun Gupta [ varun ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Varun Gupta (Inactive) made changes - 2021-03-04 17:43

Component/s		Data Definition - Alter Table [ 10114 ]
Component/s		Data Definition - Create Table [ 14503 ]
Component/s		Optimizer [ 10200 ]
Fix Version/s		10.6.0 [ 24431 ]
Fix Version/s	10.6 [ 24028 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Elena Stepanova made changes - 2021-03-07 00:17

Link

This issue causes ~~MDEV-25075~~ [ ~~MDEV-25075~~ ]

Elena Stepanova made changes - 2021-03-07 13:35

Link

This issue causes TODO-2814 [ TODO-2814 ]

Elena Stepanova made changes - 2021-03-07 13:40

Link

This issue causes TODO-2815 [ TODO-2815 ]

Ralf Gebhardt made changes - 2021-08-05 12:59

Description

h3. SYNTAX

An index can be ignored in the following way:

  1) Adding IGNORED to the key specification in CREATE TABLE
      Example

{code:sql}
CREATE TABLE t1(a INT, key key1(a) IGNORE);
{code}

  2) Adding IGNORED to the key specification in CREATE INDEX
       Example

{code:sql}
CREATE INDEX key1 on t1 (a) IGNORE;
{code}

  3) Adding IGNORED to the key in the ALTER statement

     New syntax: ALTER TABLE table_name ALTER INDEX key_name ignorability

  ignorability:
        | IGNORE_SYM
        | NOT IGNORE_SYM

    Example:
{code:sql}
ALTER TABLE t1 ADD INDEX key1 IGNORE;
{code}

h3. Specifications

* Store the information about the ignorability of the index in the FRM.
* A primary index cannot be made IGNORABLE.
* A non-nullable unique key that is promoted to a primary key cannot be made IGNORABLE.
* CHANGES in the output of SHOW CREATE TABLE and SHOW INDEXES|KEYS to show if the index is IGNORED or not.
* Introduce an optimizer switch that would allow the optimizer to not consider the ignorable indexes
   ignore_indexes: on/off [name for the switch can be reconisdered]
* An ALTER INDEX operation should use the INPLACE algorithm by default.

h3. SYNTAX

An index can be ignored in the following way:

  1) Adding IGNORED to the key specification in CREATE TABLE
      Example

{code:sql}
CREATE TABLE t1(a INT, key key1(a) IGNORE);
{code}

  2) Adding IGNORED to the key specification in CREATE INDEX
       Example

{code:sql}
CREATE INDEX key1 on t1 (a) IGNORE;
{code}

  3) Adding IGNORED to the key in the ALTER statement

     New syntax: ALTER TABLE table_name ALTER INDEX key_name ignorability

  ignorability:
        | IGNORE_SYM
        | NOT IGNORE_SYM

    Example:
{code:sql}
ALTER TABLE t1 ADD INDEX key1 IGNORE;
{code}

h3. Specifications

* Store the information about the ignorability of the index in the FRM.
* A primary index cannot be made IGNORABLE.
* A non-nullable unique key that is promoted to a primary key cannot be made IGNORABLE.
* CHANGES in the output of SHOW CREATE TABLE and SHOW INDEXES|KEYS to show if the index is IGNORED or not.
* An ALTER INDEX operation should use the INPLACE algorithm by default.

Sergei Golubchik made changes - 2021-12-06 21:22

Workflow

MariaDB v3 [ 62488 ]

MariaDB v4 [ 132487 ]

Kyle Hutchinson made changes - 2024-03-26 21:28

Link

This issue relates to ~~MDEV-33777~~ [ ~~MDEV-33777~~ ]

MariaDB Server

Make an index ignorable to the optimizer

Details

Description

SYNTAX

Specifications

Attachments

Issue Links

Activity

People

Dates

Git Integration