What you need to know about creating MySQL indexes

# Specify the index when creating the table CREATE TABLE `t_index` (
  `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key',
  `col1` int(11) NOT NULL,
  `col2` varchar(20) NOT NULL,
  `col3` varchar(50) NOT NULL,
  `col4` int(11) NOT NULL,
 `col5` varchar(50) NOT NULL,
  PRIMARY KEY (`increment_id`),
  UNIQUE KEY `uk_col1` (`col1`),
  KEY `idx_col2` (`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Test index';

# Create index (two methods)
# Normal index alter table `t_index` add index idx_col3 (col3);
 create index idx_col3 on t_index(col3);
# Unique index alter table `t_index` add unique index uk_col4 (col4);
create unique index uk_col4 on t_index(col4);
# Joint index alter table `t_index` add index idx_col3_col4 (col3,col4);
create index idx_col3_col4 on t_index(col3,col4);
# Prefix index alter table `t_index` add index idx_col5 (col5(20));
 create index idx_col5 on t_index(col5(20));

# View the table index mysql> show index from t_index;
+---------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+------------+
| t_index | 0 | PRIMARY | 1 | increment_id | A | 0 | NULL | NULL | | BTREE | | |
| t_index | 0 | uk_col1 | 1 | col1 | A | 0 | NULL | NULL | | BTREE | | |
| t_index | 1 | idx_col2 | 1 | col2 | A | 0 | NULL | NULL | | BTREE | | |
| t_index | 1 | idx_col3 | 1 | col3 | A | 0 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+------------+

# Test user permissions mysql> show grants;
+-----------------------------------------------------------------------------------------------------+
| Grants for testuser@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `testdb`.* TO 'testuser'@'%' |
+-----------------------------------------------------------------------------------------------------+

# Create index using alter table mysql> alter table `t_index` add index idx_col2 (col2);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

# create index mysql> create index idx_col3 on t_index(col3);
ERROR 1142 (42000): INDEX command denied to user 'testuser'@'localhost' for table 't_index'

# create index To create an index, you also need the index permission. Grant the index permission and then execute mysql> create index idx_col3 on t_index(col3);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0