Summary of knowledge points in MySQL indexing

Type of index:

Normal index: This is the most basic index type, no uniqueness, and so on.

Uniqueness Index: Basically the same as a normal index, but all indexed columns can only appear once and remain unique.

Primary key: The primary key is a unique index, but must be specified as "PRIMARY key".

Full-text index: The index type of the full-text index is fulltext. A full-text index can be created on a VARCHAR or text -type column.

Operation of the Index

Use the ALTER TABLE statement to create the index.

ALTER TABLE table_name ADD index index_name (column_list);

ALTER TABLE table_name add unique (column_list);

ALTER TABLE TABLE_NAME ADD PRIMARY key (column_list);

Use the CREATE index statement to add an index to the table.

CREATE INDEX index_name on table_name (column_list);

Create unique index index_name on table_name (column_list);

Delete Index

DROP INDEX index_name on table_name;

ALTER TABLE table_name DROP INDEX index_name;

ALTER TABLE table_name drop PRIMARY key;

View Index

Show index from TABLE_NAME;

How to use Indexes

1.Create an index in a field that is frequently connected

2. Indexing on asorted or grouped column

3, in the conditional expression to establish the cause (where), there are two kinds of

Indexes are used on columns of different values,

Indexes are not available on columns with fewer values

4, there are multiple columns to sort, you can create composite indexes on these columns

5, can establish short index, for example:

ALTER TABLE table_name ADD index index_name (column_list (length))

ALTER TABLE test ADD index AB (' A ' (3));

6. Columns that appear injoin need to be indexed

Precautions :

1, the index does not contain columns with null values

2. TheMySQL query uses only one index, so if An index is already used in the WHERE clause, then order by The columns in are not indexed. So the data

Do not use a sort operation if the library default sort can meet the requirements, try not to include multiple column sorting, and if necessary, create a composite index for these columns.

3.do not perform calculations on columns

4 .mysql does not use indexes when queryingwith wildcards% and _ . For example:

The index is used : SELECT * FROM mytable WHERE username like ' admin% '

Index not used : SELECT * FROM mytable WHERE username like '%admin '

Combined index

The leftmost principle of the combined index, the first field of the combined index must appear in the query group sentence, and the index will not be used.

If there is a combined index (Col_a,col_b,col_c)

This index will be used in the following situations:

Col_a = "some value";

Col_a = "Some value" and Col_b = "some value";

Col_a = "Some value" and Col_b = "Some value" and Col_c = "some value";

Col_b = "Some value" and col_a = "Some value" and Col_c = "some value";

For the last statement,MySQL automatically optimizes the appearance of the third bar ~ ~.

The following scenario does not use the index:

Col_b = "AAAAAA";

Col_b = "AAAA" and Col_c = "CCCCCC";

Summary of knowledge points in MySQL indexing