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: | |
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
[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