Database system principle and Application Tutorial （037）—— MySQL The index of （ 3、 ... and ）： Delete index

If an index is not needed , You can delete . in addition , Once the index is created, it cannot be modified , But you can delete the index and recreate it , To achieve the purpose of modifying the index .

There are two ways to delete an index ：（1） Use alter table command .（2） Use drop index command .

One 、 Delete index command

The syntax format of the delete index command is as follows ：

ALTER TABLE  Table name  DROP INDEX  Index name ;
--  or 
DROP INDEX  Index name  ON  Table name ;

Two 、 Use alter table Command delete index

The syntax is as follows ：

ALTER TABLE  Table name  DROP INDEX  Index name ;

for example ：

1、 Create tables and indexes at the same time

/*
create table t51(
    id int primary key auto_increment,
    name char(20),
    birth datetime,
    salary int,
    phone char(20),
    email varchar(50),
    index idx_name(name),
    unique index uq_phone(phone),
    index idx_name_email(name,email(10))
);
*/

mysql> create table t51(
    ->     id int primary key auto_increment,
    ->     name char(20),
    ->     birth datetime,
    ->     salary int,
    ->     phone char(20),
    ->     email varchar(50),
    ->     index idx_name(name),
    ->     unique index uq_phone(phone),
    ->     index idx_name_email(name,email(10))
    -> );
Query OK, 0 rows affected (0.04 sec)

2、 See the table t51 Index in

mysql> show index from t51\G
*************************** 1. row ***************************
        Table: t51
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: t51
   Non_unique: 0
     Key_name: uq_phone
 Seq_in_index: 1
  Column_name: phone
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: t51
   Non_unique: 1
     Key_name: idx_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 4. row ***************************
        Table: t51
   Non_unique: 1
     Key_name: idx_name_email
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 5. row ***************************
        Table: t51
   Non_unique: 1
     Key_name: idx_name_email
 Seq_in_index: 2
  Column_name: email
    Collation: A
  Cardinality: 0
     Sub_part: 10
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
5 rows in set (0.01 sec)

3、 Delete index idx_name_email

mysql> alter table t51 drop index idx_name_email;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t51\G
*************************** 1. row ***************************
        Table: t51
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: t51
   Non_unique: 0
     Key_name: uq_phone
 Seq_in_index: 1
  Column_name: phone
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: t51
   Non_unique: 1
     Key_name: idx_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
3 rows in set (0.00 sec)

3、 ... and 、 Use drop index Command delete index

The syntax is as follows ：

DROP INDEX  Index name  ON  Table name ;

for example ： Delete table t51 Index in uq_phone

mysql> drop index uq_phone on t51;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t51\G
*************************** 1. row ***************************
        Table: t51
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: t51
   Non_unique: 1
     Key_name: idx_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)