How MySQL indexes are used

One, create

CREATE Index to add a normal or unique index to a table.

CREATE INDEX index_name ON table_name (column_list) CREATE UNIQUE INDEX index_name ON table_name (column_list)

1) Build Multiple indexes

CREATE INDEX Table Name _ Field name 1_ field Name 2 on table name ( field name 1, field name 2);

use explain to see if the index is used: SELECT * FROM MyTable WHERE category_id=1 and user_id=2;

want to make select * FROM MyTable WHERE category_id=1 and user_id=2 ORDER by Adddate DESC; Working with Indexes

Optimization Method: EXPLAIN SELECT * FROM MyTable WHERE category_id=1 and user_id=2 ORDER by category_id desc,user_id desc,adddate DESC;

The primary key index cannot be created with the CREATE INDEX statement.

Second, alter

Example:

1. Add primary key (primary key index)
Mysql>alter TABLE ' table_name ' ADD PRIMARY KEY (' column ')

2. Add unique (unique index)
Mysql>alter TABLE ' table_name ' ADD UNIQUE (
' Column '
)

3. Add index (normal index)
Mysql>alter TABLE ' table_name ' ADD INDEX index_name (' column ')

4. Add fulltext (full-text index)
Mysql>alter TABLE ' table_name ' ADD fulltext (' column ')

5. Adding Multi-column indexes
Mysql>alter TABLE ' table_name ' ADD INDEX index_name (' column1 ', ' column2 ', ' column3 ')

A more detailed approach

The basic syntax for adding an index to a field in a table by using the ALTER TABLE statement is:
ALTER table < table name > ADD INDEX (< field >);

Mysql> ALTER TABLE test add index (T_NAME);
Query OK, 0 rows affected (0.17 sec)
records:0 duplicates:0 warnings:0

Third, delete the index

You can use the ALTER TABLE or DROP INDEX statement to delete an index. Similar to the CREATE INDEX statement, DROP Index can be handled as a statement inside ALTER TABLE, with the following syntax.

DROP INDEX index_name ON talbe_name ALTER TABLE table_name DROP INDEX index_name ALTER TABLE table_name DROP PRIMARY KEYIv. Viewing indexes mysql> show index from tblname; mysql> show keys from tblname;

How MySQL indexes are used