Introduction to MySQL database indexing experience in PHP development

Suppose we created a testIndex table: CREATETABLEtestIndex (I _testIDINTNOTNULL, vc_NameVARCHAR (16) NOTNULL). We randomly inserted 1000 records into the table, one of which is I _testIDvc_Name555erquan looking for vc_Name =

Suppose we created a testIndex table:
Create table testIndex (I _testID INT NOT NULL, vc_Name VARCHAR (16) NOT NULL );

We randomly inserted 1000 records into it, one of which
I _testID vc_Name
555 erquan

Find the record of vc_Name = 'erquanc'
SELECT * FROM testIndex WHERE vc_Name = 'erquanc ';
If an index has been created on vc_Name, MySql can find this record accurately without any scanning! On the contrary, MySql scans all records, that is, it needs to query 1000 times ~~ Indexes increase the query speed by 100 times.

I. index is divided into single-column indexes and composite indexes

Single-column index: an index only contains a single column. a table can have multiple single-column indexes, but this is not a combination index.
Composite index: a cable contains multiple columns.

2. check the index type first.

1. common indexes.
This is the most basic index with no restrictions. It has the following creation methods:
(1) CREATE an INDEX: create index indexName ON tableName (tableColumns (length); for CHAR and VARCHAR types, the length can be smaller than the actual length of the field; for BLOB and TEXT types, length must be specified, the same below.
(2) modify the table structure: ALTER tableName add index [indexName] ON (tableColumns (length ))
(3) when creating a TABLE, specify: create table tableName ([...], INDEX [indexName] (tableColumns (length ));

2. unique index.
It is similar to the previous 'normal Index'. The difference is that the value of the index column must be unique, but a null value is allowed. If it is a composite index, the combination of column values must be unique. It has the following creation methods:
(1) CREATE an INDEX: create unique index indexName ON tableName (tableColumns (length ))
(2) modify the table structure: ALTER tableName add unique [indexName] ON (tableColumns (length ))
(3) when creating a TABLE, specify: create table tableName ([...], UNIQUE [indexName] (tableColumns (length ));

3. primary key index
It is a special unique index and does not accept null values. Create table testIndex (I _testID int not null AUTO_INCREMENT, vc_Name VARCHAR (16) not null, primary key (I _testID )); of course, you can also use the ALTER command.
Remember: a table can only have one primary key.

4. full-text index
MySQL supports full-text indexing and full-text retrieval from the beginning of version 3.23.23. I will not discuss it here ~~

Syntax for deleting an INDEX: drop index index_name ON tableName

III. single-column index and Composite Index