Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
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.
- An ALTER INDEX operation should use the INPLACE algorithm by default.
Attachments
Issue Links
- causes
-
MDEV-25075 Ignorable index makes the resulting CREATE TABLE invalid
-
- Closed
-
- includes
-
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
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)
mysql has visible keyword now, so even not still broken mariadb 10.1 is incompatible with mysql
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, you can configure a target mysql version in workbench preferences. Set it to 5.7 to get a compatible dump.
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.
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.
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.
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.
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.
Review input provided:
https://lists.launchpad.net/maria-developers/msg12543.html
Note:
From the Server Team Lead Call: The MySQL syntax to use "INVISIBLE" should be supported as an alias
This hasn't been implemented.
Hi ralf.gebhardt@mariadb.com, right. We did not add an optimizer switch flag for this.
Here's the syntax I've implemented in the parser:
\Wshow warnings;mysql> \WShow 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: 0mysql> 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: 0mysql> alter table t1 change index idx_col1 invisible;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> alter table t1 change index idx_col1 visible;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> 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: 0mysql> 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)