Bug #72129 Expose index properties through INFORMATION_SCHEMA

• Submitted: 26 Mar 2014 2:01
• Reporter: Marcos Albe (OCA)
• Status: Open
• Category: MySQL Server: DDL
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any

[26 Mar 2014 2:01] Marcos Albe

Description:
Working with a customer he came up with the following:

Our Java application produce different versions of DDL via hibernate from the code. The mysql version of the DDL runs fine, but the Oracle version of DDL has performance issues. A closer examination found that the indexes on FK columns are automatically created on MySQL but not on Oracle. We want to produce a list if those automatically created indexes to put on source code, so that the objects on MySQL and Oracle would be the same, and hopefully the performance as well.

Oracle has a way to tell if an index is automatically created via column "flag" from the sys.obj$ table (see asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1360203050892d) or via column "property" from sys.ind$ table (see http://www.freelists.org/post/oracle-l/Naming-standards-that-lead-to-implicitexplicit-cons...) . 

So, would be nice if MySQL could exhibit the same kind of index metadata through a bit field on INFORMATION_SCHEMA.STATISTICS.

How to repeat:
USE test;
DROP TABLE IF EXISTS parent;
DROP TABLE IF EXISTS child;

CREATE TABLE `parent` (`a` INT NOT NULL PRIMARY KEY) ENGINE INNODB;

CREATE TABLE `child` (
`a` int(11) DEFAULT NULL,
KEY `fk_child` (`a`),
CONSTRAINT `fk_child` FOREIGN KEY (`a`) REFERENCES `parent` (`a`)
) ENGINE=InnoDB

SHOW CREATE TABLE child\G

DROP CREATE TABLE child\G

CREATE TABLE `child` (
`a` int(11) DEFAULT NULL,
CONSTRAINT `fk_child` FOREIGN KEY (`a`) REFERENCES `parent` (`a`)
) ENGINE=InnoDB

SHOW CREATE TABLE child\G

Table definition looks the same and there's no data in INFORMATION_SCHEMA that would allow us to differentiate explicitly created index vs implicitly created index.

Suggested fix:
Expose index metadata through information schema table.

[27 Mar 2014 13:55] Pura Vida

While there is no difference that a user can query, there is a difference
in behavior: when adding a second index on the same FK column, the
second index is added IN ADDITION to the manually created index, but
the second index REPLACES the automatically created index.

The following modified code 

USE test;
DROP TABLE IF EXISTS parent;
DROP TABLE IF EXISTS child;

CREATE TABLE `parent` (`a` INT NOT NULL PRIMARY KEY) ENGINE INNODB;

CREATE TABLE `child` (
`a` int(11) DEFAULT NULL,
KEY `fk_child` (`a`),
CONSTRAINT `fk_child` FOREIGN KEY (`a`) REFERENCES `parent` (`a`)
) ENGINE=InnoDB
;

SHOW CREATE TABLE child\G

alter table child add key fk_child2 (a);

SHOW CREATE TABLE child\G

/* show both indexes fk_child and fk_child2 */ 

DROP TABLE child\G

CREATE TABLE `child` (
`a` int(11) DEFAULT NULL,
CONSTRAINT `fk_child` FOREIGN KEY (`a`) REFERENCES `parent` (`a`)
) ENGINE=InnoDB
;

SHOW CREATE TABLE child\G

alter table child add key fk_child2 (a);

SHOW CREATE TABLE child\G

/* Only show index fk_child2 */