Description:
It seems a bit unwise to recommend dropping the PK in the following case. I believe that in situations like this both indexes (PRIMARY KEY and UNIQUE) should be kept and no rows should be returned.
How to repeat:
# Create table
CREATE TABLE test.`fund_perf_info` (
`FUND_ID` varchar(20) not null,
`FUND_UNIVERSE` varchar(10) NOT NULL,
PRIMARY KEY (`FUND_ID`,`FUND_UNIVERSE`),
UNIQUE KEY `FUND_ID` (`FUND_ID`)
);
# SQL statement
select * from sys.schema_redundant_indexes where table_schema='test' and table_name='fund_perf_info'\G
# Output
*************************** 1. row ***************************
table_schema: test
table_name: fund_perf_info
redundant_index_name: PRIMARY
redundant_index_columns: FUND_ID,FUND_UNIVERSE
redundant_index_non_unique: 0
dominant_index_name: FUND_ID
dominant_index_columns: FUND_ID
dominant_index_non_unique: 0
subpart_exists: 0
sql_drop_index: ALTER TABLE `test`.`fund_perf_info` DROP INDEX `PRIMARY`
1 row in set (0.01 sec)
Suggested fix:
To avoid this suggestion, I recommend changing the SQL statement.
Something like adding the following condition:
...
WHERE
redundant_keys.index_name != dominant_keys.index_name
AND redundant_keys.index_name NOT LIKE 'PRIMARY'
...
Description: It seems a bit unwise to recommend dropping the PK in the following case. I believe that in situations like this both indexes (PRIMARY KEY and UNIQUE) should be kept and no rows should be returned. How to repeat: # Create table CREATE TABLE test.`fund_perf_info` ( `FUND_ID` varchar(20) not null, `FUND_UNIVERSE` varchar(10) NOT NULL, PRIMARY KEY (`FUND_ID`,`FUND_UNIVERSE`), UNIQUE KEY `FUND_ID` (`FUND_ID`) ); # SQL statement select * from sys.schema_redundant_indexes where table_schema='test' and table_name='fund_perf_info'\G # Output *************************** 1. row *************************** table_schema: test table_name: fund_perf_info redundant_index_name: PRIMARY redundant_index_columns: FUND_ID,FUND_UNIVERSE redundant_index_non_unique: 0 dominant_index_name: FUND_ID dominant_index_columns: FUND_ID dominant_index_non_unique: 0 subpart_exists: 0 sql_drop_index: ALTER TABLE `test`.`fund_perf_info` DROP INDEX `PRIMARY` 1 row in set (0.01 sec) Suggested fix: To avoid this suggestion, I recommend changing the SQL statement. Something like adding the following condition: ... WHERE redundant_keys.index_name != dominant_keys.index_name AND redundant_keys.index_name NOT LIKE 'PRIMARY' ...