Mysql advanced indexes

Mysql advanced indexes

Index: A directory created for data.

Purpose: accelerate the query speed.

Negative Impact: This reduces the speed of adding, deleting, and modifying data.

Indexing principles:

1: Do not over-Index

2: add columns with the most frequent where conditions. Do not add columns with a high degree of repetition, such as gender and female;

3: Try to index the hash value. It is of little significance to add the index to an excessively concentrated value.

Index type

Common index: index only accelerates the query speed.

Unique index: the value on the unique index row cannot be repeated.

Primary key Index: The primary key cannot be repeated.

The primary key must be unique, but the unique index is not necessarily the primary key.

A table can have only one primary key, but one or more unique indexes can be used.

Full-text index: fulltext index is of little significance to Chinese Characters in mysql by default. Third-party solutions are generally used.

There are spaces and punctuation marks in English to split words into indexes.

For Chinese, there is no space to separate words. mysql cannot recognize each Chinese word.

(The above three indexes are used for column values, but full-text indexes can be used for a word in a value, such as an article ,) full-text indexing does not target very frequent words, such as this, is, you, my, and so on.

Index length: Specify part of a column as an index.

Multi-column index: Two or more column values are regarded as the whole and used as the index. For an English website, the first name and last name are regarded as a whole. In this case, only the first name in the where condition can play a role, but the last name does not. This is the left prefix rule.

Redundant Index: A column has multiple indexes. For example, you can create another index on the last name.

Create an index

You can directly declare the index when creating a table, that is, after the column declaration is complete, declare the index.

For example:

Create table test5 (

Id int,

Username varchar (20 ),

School varchar (20 ),

Intro text,

Primary key (id ),

Unique (username ),

Index (school ),

Fulltext (intro)

) Engine myisam charset utf8;

View All indexes on a table

Show index from Table Name

Create an index

Alter table name add index/unique/fulltext [index name] (column name)

Alter table name add primary key (column name) // do not add the index name, because the primary key has only one

Delete add index

Delete non-primary key indexes: Alter table Name drop/add index name;

Delete primary key: alter table Name drop/add primary key