# Secondary Index

__*Secondary Index*__

+ Secondary index: an index other than the primary key 
+ Unique secondary index: secondary index with constraints
+ To create a secondary index: 
  + Use the **`INDEX`** or **`KEY`** keyword when creating a table
  + For existing tables:
    + **`ALTER TABLE ... ADD [UNIQUE] INDEX|KEY ...`** 
    + **`CREATE INDEX ... ON ...`** 

```
tidb> CREATE TABLE test.ggg (id INT, name CHAR(30), ~!@KEY@!~(name));

tidb> ~!@CREATE INDEX@!~ ggg_idx ON test.ggg (id);

tidb> CREATE TABLE test.gg (id INT, id2 INT, name CHAR(30), ~!@INDEX@!~(name));

tidb> ALTER TABLE test.gg ~!@ADD KEY@!~ (id);

tidb> ALTER TABLE test.gg ~!@ADD INDEX@!~ (id2);
```
------------------------------------------------------------
```
tidb> SHOW CREATE TABLE test.gg\G
*************************** 1. row ***************************
       Table: gg
Create Table: CREATE TABLE `gg` (
  `id` int(11) DEFAULT NULL,
  `id2` int(11) DEFAULT NULL,
  `name` char(30) DEFAULT NULL,
  KEY `name` (`name`),
  KEY `id` (`id`),
  KEY `id2` (`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)
```