MySQL CREATE index and index deletion

This article is about MySQL Tutorial create index and index Delete Oh, this tutorial is about MySQL 5.0 supported index types, and simply introduces the original design of the index, which is the most common tool used in the database tutorial to raise performance.

One

All mysql indexed column types can be indexed, and using indexes for related classes can improve select query performance, depending on the number of MySQL indexes , which can be the maximum index and the minimum index, and each storage engine supports at least 16 of the index per table. The total index length is 256 bytes.

MySim and InnoDB storage engine tables default to create indexes are Btree index, currently MySQL does not support function index, but support the leading edge index, the first n characters of the field to create an index

Second, MySQL CREATE INDEX 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 in creating an index, you need to modify the MySQL index we can use alert to modify the index, the syntax and CREATE index created indexed almost, we do not say, you can view the relevant manuals.

Now let's look at a tutorial on creating an index instance of MySQL .

Mysql>create index CityName on city (2);

Query ok,600 rows Affected (0.26 sec)

records:600 duplicates:0 Warings 0:

We now come to the city as a condition for the query, as below.

->explain SELECT * from city where city = ' www.111cn.net ' G

Id:1

......

Possible_keys:cityname

Key:cityname

Okay, now let's look at MySQL. Delete an index instance

Drop indexname on tablename

instance, I'm now going to delete just create the city index

>drop index cityname on city;

Query OK, ...

All right, original article of this site

Reprinted annotated from http://www.111cn.net/