What you need to know about creating MySQL indexes

Preface:
1. Create index method
2. Permissions required to create an index

Preface:

In MySQL, basically every table has an index, and sometimes you need to add different indexes based on different business scenarios. The establishment of indexes is very important for the efficient operation of the database. This article will introduce the knowledge and precautions related to creating indexes.

1. Create index method

You can create an index when you create a table, or you can use the alter table or create index statement to create an index after the table is created. The following are some common index creation scenarios.

# 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 | | |
+---------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+------------+

2. Permissions required to create an index

If you are not using the root account, you need to consider the permission issue when creating an index. Do you only need create and alter permissions? Let’s take a closer look.

# 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

From the above test, we can see that the alter permission is required to create an index using the alter table method, and the index permission is required to create an index using the create index method.

In addition, you can also delete indexes using alter table `tb_name` drop index xxx and drop index xxx on tb_name, which require alter and index permissions respectively.

The obvious advantage of indexes is that they can speed up queries, but creating indexes also comes at a cost. First, each time an index is created, a B+ tree must be created for it, which will take up additional storage space; secondly, when the data in the table is added, deleted, or modified, the index also needs to be dynamically maintained, which reduces the speed of data maintenance. Therefore, we still need to consider the business when creating indexes. It is recommended not to add too many indexes in a table.

The above is the detailed information you need to know about creating MySQL indexes. For more information about creating MySQL indexes, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in: