[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

James Briggs added a comment - 2014-12-15 23:57 - edited

Here's the syntax I've implemented in the parser:

\W

use test

drop table if exists t1;

create table t1 (id int, col1 char(3), key idx_id (id) visible) ENGINE=InnoDB DEFAULT CHARSET=latin1;

create index idx_col1 on t1 (col1) visible;

show create table t1;

insert into t1 (id, col1) values (1,'abc'), (2, 'def');

alter table t1 change index idx_col1 invisible;

alter table t1 change index idx_col1 visible;

alter table t1 change index idx_id invisible, change index idx_col1 invisible;

select id, col1 from t1 order by id;

drop table if exists t1;

show warnings;

mysql> \W

Show warnings enabled.

mysql> drop table if exists t1;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

Note (Code 1051): Unknown table 'test.t1'

mysql> create table t1 (id int, col1 char(3), key idx_id (id) visible) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Query OK, 0 rows affected (0.20 sec)

mysql> create index idx_col1 on t1 (col1) visible;

Query OK, 0 rows affected (0.07 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1;

| Table | Create Table                                                                                                                                                            |

| t1    | CREATE TABLE `t1` (

  `id` int(11) DEFAULT NULL,

  `col1` char(3) DEFAULT NULL,

  KEY `idx_id` (`id`),

  KEY `idx_col1` (`col1`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

1 row in set (0.00 sec)

mysql> insert into t1 (id, col1) values (1,'abc'), (2, 'def');

Query OK, 2 rows affected (0.01 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table t1 change index idx_col1 invisible;

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t1 change index idx_col1 visible;

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t1 change index idx_id invisible, change index idx_col1 invisible;

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> select id, col1 from t1 order by id;

+------+------+

| id   | col1 |

+------+------+

|    1 | abc  |

|    2 | def  |

+------+------+

2 rows in set (0.00 sec)

mysql> drop table if exists t1;

Query OK, 0 rows affected, 1 warning (0.03 sec)

James Briggs added a comment - 2014-12-15 23:57 - edited Here's the syntax I've implemented in the parser: \W use test drop table if exists t1; create table t1 (id int , col1 char (3), key idx_id (id) visible) ENGINE=InnoDB DEFAULT CHARSET=latin1; create index idx_col1 on t1 (col1) visible; show create table t1; insert into t1 (id, col1) values (1, 'abc' ), (2, 'def' ); alter table t1 change index idx_col1 invisible; alter table t1 change index idx_col1 visible; alter table t1 change index idx_id invisible, change index idx_col1 invisible; select id, col1 from t1 order by id; drop table if exists t1; show warnings; mysql> \W Show warnings enabled. mysql> drop table if exists t1; Query OK, 0 rows affected, 2 warnings (0.00 sec) Note (Code 1051): Unknown table 'test.t1' mysql> create table t1 (id int, col1 char(3), key idx_id (id) visible) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.20 sec) mysql> create index idx_col1 on t1 (col1) visible; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t1; | Table | Create Table | | t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `col1` char(3) DEFAULT NULL, KEY `idx_id` (`id`), KEY `idx_col1` (`col1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 1 row in set (0.00 sec) mysql> insert into t1 (id, col1) values (1,'abc'), (2, 'def'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> alter table t1 change index idx_col1 invisible; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 change index idx_col1 visible; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 change index idx_id invisible, change index idx_col1 invisible; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select id, col1 from t1 order by id; +------+------+ | id | col1 | +------+------+ | 1 | abc | | 2 | def | +------+------+ 2 rows in set (0.00 sec) mysql> drop table if exists t1; Query OK, 0 rows affected, 1 warning (0.03 sec)

James Briggs added a comment - 2014-12-16 00:10 - edited

The Oracle 11g documentation is fuzzy on whether invisible indexes are session or global or both. If anybody has any thoughts on that, please add a comment.

In MySQL, various index properties are scoped like this:

set foreign_key_checks (session or global)
alter table disable keys (MyISAM-only, global)
create temporary table (session)

James Briggs added a comment - 2014-12-16 00:10 - edited The Oracle 11g documentation is fuzzy on whether invisible indexes are session or global or both. If anybody has any thoughts on that, please add a comment. In MySQL, various index properties are scoped like this: set foreign_key_checks (session or global) alter table disable keys (MyISAM-only, global) create temporary table (session)

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

luntik2012 added a comment - 2018-10-28 09:58

mysql has visible keyword now, so even not still broken mariadb 10.1 is incompatible with mysql

luntik2012 added a comment - 2018-10-28 09:58 mysql has visible keyword now, so even not still broken mariadb 10.1 is incompatible with mysql

Colm Smyth added a comment - 2018-12-28 10:00

Upvoting this issue as it blocks compatibility with recent versions of MySQL Workbench, and as it is a straightforward feature that has already been prototyped.

Colm Smyth added a comment - 2018-12-28 10:00 Upvoting this issue as it blocks compatibility with recent versions of MySQL Workbench, and as it is a straightforward feature that has already been prototyped.

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 ]

Sergei Golubchik added a comment - 2020-04-28 12:43

colm_smyth, you can configure a target mysql version in workbench preferences. Set it to 5.7 to get a compatible dump.

Sergei Golubchik added a comment - 2020-04-28 12:43 colm_smyth , you can configure a target mysql version in workbench preferences. Set it to 5.7 to get a compatible dump.

Sergei Golubchik added a comment - 2020-04-28 13:22 - edited

varun, we cannot really call them INVISIBLE, because we already use the concept of invisibility for something completely different. Perhaps, "IGNORED"? That exactly explains what they are, and also matches the IGNORE INDEX syntax in the FROM clause.

Sergei Golubchik added a comment - 2020-04-28 13:22 - edited varun , we cannot really call them INVISIBLE, because we already use the concept of invisibility for something completely different. Perhaps, "IGNORED"? That exactly explains what they are, and also matches the IGNORE INDEX syntax in the FROM clause.

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.

Rick James added a comment - 2020-05-04 16:40

While I understand that IGNORE is a better keyword than INVISIBLE, I worry that MariaDB's incompatibility with MySQL will be made worse. It means that transferring some datasets between MariaDB and MySQL, in either direction, will lead to errors fixable only by hand-editing of the dump.

Perhaps a partial solution is for loading the file can accept either keyword. Or a commandline switch can allow for ignoring the conflicting keyword.

Note that Workbench raised the problem by adding [unnecessarily] "VISIBLE" when making dumps. "mysqldump" should not include VISIBLE.

Rick James added a comment - 2020-05-04 16:40 While I understand that IGNORE is a better keyword than INVISIBLE, I worry that MariaDB's incompatibility with MySQL will be made worse. It means that transferring some datasets between MariaDB and MySQL, in either direction, will lead to errors fixable only by hand-editing of the dump. Perhaps a partial solution is for loading the file can accept either keyword. Or a commandline switch can allow for ignoring the conflicting keyword. Note that Workbench raised the problem by adding [unnecessarily] "VISIBLE" when making dumps. "mysqldump" should not include VISIBLE.

Colm Smyth added a comment - 2020-05-04 20:31

There is no logical conflict between the concept of an invisible column (to SELECT) and an invisible (to the optimizer) index. If there is no syntactic issue, it would be better to use the same INVISIBLE keyword to maximise compatibility with MySQL as it is important for MariaDB to be able to simplify migration and adoption for former MySQL users.

Colm Smyth added a comment - 2020-05-04 20:31 There is no logical conflict between the concept of an invisible column (to SELECT) and an invisible (to the optimizer) index. If there is no syntactic issue, it would be better to use the same INVISIBLE keyword to maximise compatibility with MySQL as it is important for MariaDB to be able to simplify migration and adoption for former MySQL users.

Sergei Golubchik added a comment - 2020-05-05 08:54

rjasdfiii, VISIBLE is already silently parsed and ignored after ~~MDEV-22199~~. Unless one actually tries to load a dump that has "invisible" indexes, there is no compatibility issue here. Perhaps we can do this "partial solution" or may them work only in some kind of "mysql" mode.

colm_smyth, unfortunately, there is. Currently invisible indexes are indexes that only index invisible columns. There can be columns so invisible to the user that even SHOW CREATE TABLE doesn't show then, and indexes that index them are not shown either. Although they're perfectly "visible" to the optimizer and it is allowed to use them to optimize the query.

Sergei Golubchik added a comment - 2020-05-05 08:54 rjasdfiii , VISIBLE is already silently parsed and ignored after MDEV-22199 . Unless one actually tries to load a dump that has "invisible" indexes, there is no compatibility issue here. Perhaps we can do this "partial solution" or may them work only in some kind of "mysql" mode. colm_smyth , unfortunately, there is. Currently invisible indexes are indexes that only index invisible columns. There can be columns so invisible to the user that even SHOW CREATE TABLE doesn't show then, and indexes that index them are not shown either. Although they're perfectly "visible" to the optimizer and it is allowed to use them to optimize the query.

Rick James added a comment - 2020-05-05 16:57

Thanks, Colm and Sergei. In addition to the invisible index on an invisible column, I can think of two others: The hidden 6-byte PK that InnoDB uses as a last resort. And the extra column added for InnoDB FULLTEXT.

That's 3 cases. Is there a list somewhere of all such odd indexes? Perhaps such a list would be made as a checklist on whether all cases are being handled for this feature request and other situations.

Rick James added a comment - 2020-05-05 16:57 Thanks, Colm and Sergei. In addition to the invisible index on an invisible column, I can think of two others: The hidden 6-byte PK that InnoDB uses as a last resort. And the extra column added for InnoDB FULLTEXT. That's 3 cases. Is there a list somewhere of all such odd indexes? Perhaps such a list would be made as a checklist on whether all cases are being handled for this feature request and other situations.

Varun Gupta (Inactive) added a comment - 2020-05-06 12:28

The patch is in the branch 10.5-mdev7317

Varun Gupta (Inactive) added a comment - 2020-05-06 12:28 The patch is in the branch 10.5-mdev7317

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 added a comment - 2021-02-24 18:15

Review input provided:
https://lists.launchpad.net/maria-developers/msg12543.html

Sergei Petrunia added a comment - 2021-02-24 18:15 Review input provided: https://lists.launchpad.net/maria-developers/msg12543.html

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 ]

Sergei Petrunia added a comment - 2021-05-11 17:15

Note:

From the Server Team Lead Call: The MySQL syntax to use "INVISIBLE" should be supported as an alias

This hasn't been implemented.

Sergei Petrunia added a comment - 2021-05-11 17:15 Note: From the Server Team Lead Call: The MySQL syntax to use "INVISIBLE" should be supported as an alias This hasn't been implemented.

Sergei Petrunia added a comment - 2021-08-05 12:44

Hi ralf.gebhardt@mariadb.com, right. We did not add an optimizer switch flag for this.

Sergei Petrunia added a comment - 2021-08-05 12:44 Hi ralf.gebhardt@mariadb.com , right. We did not add an optimizer switch flag for this.

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