Skip to content

CREATE INDEX

Grammar Description

Create indexes in tables to query data more quickly and efficiently.

You cannot see the index, the index can only be used to speed up searches/queries.

Updating a table with an index takes more time than updating a table without an index, because the index itself also needs to be updated. Therefore, it is ideal to just create an index on the columns (and tables) that are often searched.

There are several common types of indexes, namely:

  • Primary Key: that is, the primary key index, that is, the index identified on the primary key column.
  • Unique Key: that is, the index on a non-primary key column, which ensures that each value of the column is unique.
  • Secondary Index: that is, the secondary index, that is, the index identified on non-primary key columns.

Grammar Structure

> CREATE [UNIQUE] INDEX index_name
ON tbl_name (key_part,...)
COMMENT 'string'

Syntax interpretation

CREATE UNIQUE INDEX Syntax

Create a unique index on the table. Duplicate values ​​are not allowed: a unique index means that two rows cannot have the same index value.

CREATE INDEX Syntax

Create a secondary index on the table. Repeat values ​​and Null values ​​can be used.

Example

  • Example 1:
drop table if exists t1;
create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
insert into t1 values(1,"Abby", 24);
insert into t1 values(2,"Bob", 25);
insert into t1 values(3,"Carol", 23);
insert into t1 values(4,"Dora", 29);
create unique index idx on t1(name);
mysql> select * from t1;
+------+------+
| id | name | age |
+------+------+
| 1 | Abby | 24 |
| 2 | Bob | 25 |
| 3 | Carol | 23 |
| 4 | Dora | 29 |
+------+------+
4 rows in set (0.00 sec)

mysql> show create table t1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| t1 | CREATE TABLE `t1` (
`id` INT NOT NULL,
`name` VARCHAR(255) DEFAULT NULL,
`age` INT DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx` (`name`)
) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)


create table t2 (
col1 bigint primary key,
col2 varchar(25),
col3 float,
col4 varchar(50)
);
create unique index idx on t2(col2) comment 'create varchar index';
insert into t2 values(1,"Abby", 24,'zbcvdf');
insert into t2 values(2,"Bob", 25,'zbcvdf');
insert into t2 values(3,"Carol", 23,'zbcvdf');
insert into t2 values(4,"Dora", 29,'zbcvdf');
mysql> select * from t2;
+------+------+-------+
| col1 | col2 | col3 | col4 |
+------+------+-------+
| 1 | Abby | 24 | zbcvdf |
| 2 | Bob | 25 | zbcvdf |
| 3 | Carol | 23 | zbcvdf |
| 4 | Dora | 29 | zbcvdf |
+------+------+-------+
4 rows in set (0.00 sec)
mysql> show create table t2;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| t2 | CREATE TABLE `t2` (
`col1` BIGINT NOT NULL,
`col2` VARCHAR(25) DEFAULT NULL,
`col3` FLOAT DEFAULT NULL,
`col4` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`col1`),
UNIQUE KEY `idx` (`col2`) COMMENT `create varchar index`
) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)
  • Example 2: Create a secondary index and query
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    Salary INT
);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (1, 'John', 'Doe', 'HR', 50000);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (2, 'Jane', 'Smith', 'IT', 60000);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (3, 'Mark', 'Johnson', 'IT', 55000);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary)
VALUES (4, 'Mary', 'Brown', 'Sales', 48000);

mysql> CREATE INDEX DepartmentIndex ON Employees (Department);
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM Employees WHERE Department = 'IT';
+--------------------------------------------------------------------------+
| employeeid | firstname | lastname | department | salary |
+--------------------------------------------------------------------------+|          2 | Jane      | Smith    | IT         |  60000 |
|          3 | Mark      | Johnson  | IT         |  55000 |
+------------+-----------+----------+------------+--------+
2 rows in set (0.00 sec)