Use of MySQL indexes

MyD is a file that stores data, and Myi is an index file.
The index improves the query speed, reduces the speed of adding and deleting, not the more the better.
Type of index:
1, key, general index
2. Unique key, single index
CREATE TABLE T12 (
Name Char (10),
Email char (20),
Key name (name),
Unique key Email (email)
);
3, primary key, primary key index
Only one primary key can be found in a single table
CREATE TABLE T23 (
ID int unsigned auto_increment primary key
);
4. Fulltext Full-text index
The full-text index is not valid in the Chinese environment.
Chinese environment, need word breaker + index is effective, generally with third-party solutions, such as Sphinx
CREATE TABLE T24 (
Name Char (10),
Email char (20),
Key name (name),
Unique Key Email (email (10))//can set index length to optimize index
);
5. Multi-column index
CREATE TABLE T25 (
Xing Char (10),
Ming Char (10),
Key XM (xing,ming)//Multi-column index
);
Left prefix rule, the left side can use index, right fixed left do not know to use index
6, redundant index is on a column, there may be multiple indexes.
Index operations
1. View the index of a table
Show index from table name
Show CREATE TABLE table name
View the Build table statement, followed by the index
2. Delete Index
Which index of the table is deleted
ALTER TABLE t23 DROP INDEX name
Drop Index name on T23
3. Add Index
ALTER TABLE T23 add Index/unique xm (xing,ming);
4. Add primary key Index
ALTER TABLE T23 Add primary KEY (id[primary key name]);
5. Delete primary KEY index
ALTER TABLE t23 drop PRIMARY key;

Use of MySQL indexes