MySQL can actually duplicate the index on the same field, Oracle is not. Need to install Pt-query-digest.
[Email protected] ~]# pt-duplicate-key-checker-h10.10.11.208-uroot-proot-p3316--CHARSET=GBK
# ########################################################################
# Test.app_chat_message / / nbsp
# ####################################### #################################
# Idx_app_chat_message_to_user_from_user is a left-prefix of Index 3
# Key Definitions:
# key ' Idx_app_chat_message_to_user_from_user ' (' To_user ', ' From_user '),
# key ' Index 3 ' (' To_user ', ' from_user ', ' message_id '),
# Column types:
# ' to_user ' varchar (not nu) ll default '
# ' from_user ' varchar (+) NOT null default '
# &NBSP ; ' message_id ' varchar (+) NOT null default "
# to remove this duplicate index, execute:
ALTER TABLE ' test '. ' App_chat_message ' DROP INDEX ' idx_app_chat_message_to_user_from_user ';
# index 2 is a left-prefix of index 3
# Key Definitions:
# KEY ' Index 2 ' (' To_user ', ' from_user '),
# KEY ' Index 3 ' (' To_user ', ' from_user ', ' message_id '),
# Column Types:
# ' to_user ' varchar () NOT NULL default '
# ' from_user ' varchar () NOT NULL default '
# ' message_id ' varchar (+) NOT null default '
# To remove this duplicate index, execute:
ALTER TABLE ' test '. ' app_chat_message ' DROP index ' index 2 ';
# ########################################################################
# Test.pub_user
# ########################################################################
# Phone is a duplicate of Phone_idx
# Key Definitions:
# KEY ' phone ' (' phone '),
# KEY ' phone_idx ' (' phone '),
# Column Types:
# ' phone ' varchar (COLLATE utf8_bin default null)
# To remove this duplicate index, execute:
ALTER TABLE ' test '. ' Pub_user ' DROP INDEX ' phone ';
# Email is a duplicate of Email_idx
# Key Definitions:
# KEY ' email ' (' email '),
# KEY ' email_idx ' (' email '),
# Column Types:
# ' email ' varchar (collate utf8_bin default null)
# To remove this duplicate index, execute:
ALTER TABLE ' test '. ' Pub_user ' DROP INDEX ' email ';
# account is a duplicate of Account_idx
# Key Definitions:
# KEY ' account ' (' account '),
# KEY ' Account_idx ' (' account '),
# Column Types:
# ' account ' varchar (collate utf8_bin default null)
# To remove this duplicate index, execute:
ALTER TABLE ' test '. ' Pub_user ' DROP INDEX ' account ';
#########################################################################
.........................................................................
.........................................................................
# Size Duplicate Indexes 20358902596
# Total Duplicate Indexes 17
# Total Indexes 664
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
MySQL detects duplicate indexes
