mysql--Index

First, the simple operation of the index (increase and deletion of the search)

Create an index

CREATE [unique| Fulltext| SPATIAL] INDEX index_name

[USING Index_type]

on table_name (index_col_name,...)

[unique| Fulltext| Spatial]: Represents the type of index created , representing a unique index, a full-text index, and a spatial index, respectively;

[USING Index_type]: Indicates type of index , the index type has a btree index and a hash index.

The storage engine can only use Btree in tables MyISAM and InnoDB;

Btree Index and hash index can be used in tables where the storage engine is memory and heap

Note: Another way to create an index syntax

ALTER TABLE table_nameADD [unique| Fulltext| SPATIAL]INDEX [index_name] [Index_type] (index_col_name,...)

View Index

SHOW INDEX from table_name

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

Delete Index

- -Deletes the index of the specified name in the specified table

ALTER   Span style= "font-size:12px;font-family: ' Courier New '; Color:rgb (0,0,255); Background-color:rgb (255,255,255);" >table   table_name DROP   INDEX   index_name;

modifying indexes

There is no instruction to modify the index in MySQL, in general, delete the original index directly, re-establish the same name index.

Example: Adding and Deleting indexes: performing add/drop that index on that table

Add index

CREATE UNIQUE Index Index_prizecide on Bsy_prizeinfo (prizecode);

View Index

Show Index from Bsy_prizeinfo;

Delete Index

ALTER TABLE Bsy_prizeinfo DROP Index Index_prizecode

Add index

Alter table bsy_prizeinfo add UNIQUE index index_prizecode (prizecode);

Ii. type of index

mysql--Index