MySQL Advanced index

Index : is the directory created for the data .

function : can speed up the query

Negative effect : reduce the speed of adding and deleting .

Principles of Index creation :

1: do not over-index

2: Add on the column with the most frequent where condition . do not add to the column with high repetition, such as gender and sex;

3: as far as possible to index the hash value , too concentrated value index is not very meaningful .

Type of index

Normal index : index simply speeds up the query .

Unique index : The value on the unique index line cannot be duplicated

PRIMARY KEY index : Primary key cannot be duplicated .

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

There can be only one primary key on a table, but one or more unique indexes may be used .

Full-text index : Fulltext index in the default case of MySQL , the Chinese is not very meaningful . General use of third-party solutions

Because there are spaces in English, punctuation is broken down into words , and then the words are indexed .

For Chinese, there are no spaces to separate words , and MySQL does not recognize each Chinese word .

(above3kind of index,Works on the values of the columns,but full-text indexing,you can target a word in a value,For example an article,)Full-text indexing is not indexed for very frequent words,asThis , was, you, mywait.

Index Length : Specifies a column of partial content as an index

Multi-column index : two or more column values as a whole, as an index. In the English site,first name, lastname as a whole, at this time,thewhere condition only first Name can work,lastname does not work, that is, as the left prefix rule.

Redundant indexes : A column has multiple indexes, such as the index on the last name above.

Build an index

You can declare an index directly when you are building a table, that is, after the column declaration is complete .

For example the following :

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 single table

Show index from table name

Build an index

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

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

Remove Add Index

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

Delete PRIMARY key : ALTER TABLE name Drop/add primary Key

MySQL Advanced index