Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the methods and steps to create an index". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what are the methods and steps of creating an index.

1. Index creation method

The index can be specified when the table is created, or it can be created using alter table or create index statements after the table is created. The following are several common index creation scenarios.

# specify the index CREATE TABLE `tindex` (`colid` int (11) NOT NULL AUTO_INCREMENT COMMENT 'self-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 (`colid`), UNIQUE KEY `uk_ col1` (`col1`), KEY `idx_ col2` (`col2`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' test index when creating the table # create an index (two methods) # General index alter table `tindex` add index idx_col3 (col3); create index idx_col3 on t_index (col3); # unique index alter table `tindex` add unique index uk_col4 (col4); create unique index uk_col4 on t_index (col4); # Joint index alter table `tindex` add index idx_col3_col4 (col3,col4); create index idx_col3_col4 on t_index (col3,col4) # prefix index alter table `tindex` add index idx_col5 (col5 (20)); create index idx_col5 on t_index (col5 (20)); # View 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 a root account, you have to consider the permission to create the index. do you need create and alter permissions? Let's take a look at it in detail.

# Test the user's 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 mysql > alter table `tindex` add index idx_col2 (col2) in +-+ # alter table mode Query OK, 0 rows affected (0.05sec) Records: 0 Duplicates: 0 Warnings: 0 # create index create index mysql > create index idx_col3 on t_index (col3); ERROR 1142 (42000): INDEX command denied to user' testuser'@'localhost' for table'tcreate index'# create index also requires index permission to grant index permission before executing mysql > create index idx_col3 on t_index (col3); Query OK, 0 rows affected (0 sec) Records: 0 Duplicates: 0 Warnings: 0

As can be seen from the above tests, alter permission is required to create an index using alter table, and index permission is required to create an index using create index.

In addition, you can also use alter table `tb_ name` drop index xxx and drop index xxx on tb_name to delete an index, which requires alter and index permissions, respectively.

The obvious advantage of an index is that it can speed up the query, but creating an index also comes at a cost. First of all, each index has to build a B+ tree for it, which will take up additional storage space; secondly, when the data in the table is added, deleted and modified, the index also needs dynamic maintenance, which reduces the speed of data maintenance. Therefore, when we create an index, we still need to consider according to the business, and it is recommended not to add too many indexes to a table.

At this point, I believe you have a deeper understanding of "what are the methods and steps of creating an index?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.