Create an index and delete an index in mysql

This article introduces how to create and delete indexes in mysql. This tutorial only describes the index types supported by mysql 5.0, and briefly introduces the original design of indexes, indexes are the most commonly used tools in databases to improve performance.

This article is aboutCreate an index in the mysql tutorialThis tutorial only describes the index types supported by mysql 5.0 and briefly introduces the original index design. Index is the most common tool used in database tutorials to improve performance.

I,

All mysql index column types can be indexed. Using indexes for related classes can improve the performance of select queries.Mysql IndexNumber, which can be the maximum index and the minimum index. Each storage engine supports at least 16 indexes for each table. The index length is 256 bytes.

By default, indexes created for tables of mysim and innodb Storage engine are both btree indexes. Currently, mysql does not support function indexes, but supports leading-edge indexes. It specifies the first N characters of a field.Create an index

Ii. mysql index creation syntax

Create [unioun | fulltext | spatial] index indexname [using indextype] on tablename (tablenamecol)

Index_col_name:

Col_name [(length)] [asc | desc]

If you make a mistake when creating an index, You need to modify the mysql index. We can use alert to modify the index.Create indexWe will not talk about it. You can check the relevant manual.

Let's take a lookTutorial on creating an index instance for mysql.

Mysql> create index cityname on city (2 ));

Query OK, 600 rows affected (0.26 sec)

Records: 600 Duplicates: 0 Warings 0:

Now we can query the data by city, as shown in the following figure.

-> Explain select * from city where city = 'www. bKjia. c0m' G

Id: 1

......

Possible_keys: cityname

Key: cityname

Now let's take a look.Mysql deletes an indexAnd other instances

Drop indexname on tablename

Instance, now I want to delete the city index just created

> Drop index cityname on city;

Query OK ,.....

All right, original article on this site

Reprinted from http://www.bKjia. c0m/