Bug #89802 MySQL Table key/index position order - No possibility to change
Submitted: 26 Feb 2018 11:21 Modified: 27 Feb 2018 12:04
Reporter: Roberto Caiola Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.6.35-81.0 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: ALTER TABLE, CREATE TABLE, index position order, key position order

[26 Feb 2018 11:21] Roberto Caiola
Description:
Hi,

I am trying to change the order of the KEYs so that the schema is exactly the same between two databases.

I am doing a SHOW CREATE and it returns the following:

SHOW CREATE TABLE db1.mytable;
(...)
  PRIMARY KEY (`idadesao`),
  KEY `loja` (`loja`),
  KEY `idprovincia` (`idprovincia`),
  KEY `idmunicipio` (`idmunicipio`),
  KEY `idcomuna` (`idcomuna`),
  KEY `idadesaoitem` (`idadesaoitem`),
  KEY `idadesaoproduto` (`idadesaoproduto`),
  KEY `idutilizador` (`idutilizador`),
  KEY `idcliente` (`idcliente`),
  KEY `idpromotor` (`idpromotor`),
  KEY `origem` (`origem`),
  KEY `dataadesao` (`dataadesao`),
(...)

SHOW CREATE TABLE db2.mytable;
(...)
  PRIMARY KEY (`idadesao`),
  KEY `dataadesao` (`dataadesao`),
  KEY `loja` (`loja`),
  KEY `origem` (`origem`),
  KEY `idprovincia` (`idprovincia`),
  KEY `idmunicipio` (`idmunicipio`),
  KEY `idcomuna` (`idcomuna`),
  KEY `idadesaoitem` (`idadesaoitem`),
  KEY `idadesaoproduto` (`idadesaoproduto`),
  KEY `idutilizador` (`idutilizador`),
  KEY `idcliente` (`idcliente`),
  KEY `idpromotor` (`idpromotor`),
(...)

In the second table, e.g. the KEY `dataadesao` (`dataadesao`), show be on the bottom.

It seems that the order/positions of the keys cannot be changed.

How to repeat:
Create a table with several keys and do not add the keys with the same order in the SQL script.

Suggested fix:
Ability to specify ALTER the KEY with AFTER KEY
[27 Feb 2018 11:50] Roberto Caiola
Example:

DROP TABLE IF EXISTS tmpkeys1;

CREATE TABLE `tmpkeys1` (
	`a` INT NULL,
	`b` INT NULL,
	`c` INT NULL
)
COLLATE='utf8_general_ci';

ALTER TABLE `tmpkeys1` ADD INDEX `a` (`a`);
ALTER TABLE `tmpkeys1` ADD INDEX `b` (`b`);
ALTER TABLE `tmpkeys1` ADD INDEX `c` (`c`);

DROP TABLE IF EXISTS tmpkeys2;

CREATE TABLE `tmpkeys2` (
	`a` INT NULL,
	`b` INT NULL,
	`c` INT NULL
)
COLLATE='utf8_general_ci';

ALTER TABLE `tmpkeys2` ADD INDEX `c` (`c`);
ALTER TABLE `tmpkeys2` ADD INDEX `a` (`a`);
ALTER TABLE `tmpkeys2` ADD INDEX `b` (`b`);

SHOW CREATE TABLE tmpkeys1;
SHOW CREATE TABLE tmpkeys2;

How can I order the keys from tmpkeys2 in sequence a, b, c the same way as in the table tmpkeys1 in the SHOW CREATE TABLE?
[27 Feb 2018 12:04] MySQL Verification Team
Hi,
It is not possible to change the order of indexes in a table. The order of indexes (differently from order of columns) is not really important for anything wrt how SQL works, the only difference really is how show create will look.

If you need to rearrange them you have to drop them and create in order you want, but note that comparing show create output is not a proper way to compare if two tables have same structure as nothing in the sql standard nor in mysql promisses that show create will keep the existing format; also different backup/dump tools can generate create table differently, ignoring this order or using a totally different format. Depending on what you want to achieve you should find a proper, sustainable, way to do it. 

Best regards
Bogdan