On the experience of MySQL database indexing in PHP development

If we create a testindex table:

CREATE TABLE Testindex (i_testid INT not null,vc_name VARCHAR () not NULL);

We randomly inserted 1000 records into it, one of which

I_testid Vc_name

555 Erquan

Looking for records for vc_name= "Erquan"

SELECT * from Testindex WHERE vc_name= ' Erquan ';

, if the index has been established on the Vc_name, MySQL does not need any scanning, that is, accurate to find the record! Instead, MySQL will scan all records, that is, to query 1000 times AH ~ ~ can be indexed to improve the query speed 100 times times.

Index and combined index

Single-column Index: An index contains only a single column, and a table can have multiple single-column indexes, but this is not a combined index.

Combined index: A cord contains multiple columns.

Second, introduce the type of index

1. Normal index.

This is the most basic index, and it has no limitations. It has the following ways to create:

(1) CREATE INDEX: CREATE INDEX IndexName on tablename (tablecolumns length); If it is a Char,varchar type, length can be less than the actual length of the field; if it is a blob and TEXT Type, you must specify length, below.

(2) Modify table structure: ALTER tablename ADD INDEX [IndexName] On (tablecolumns (length))

(3) Specify directly when creating a table: Create TABLE TableName ([...], INDEX [IndexName] (tablecolumns (length));

2. Unique index.

It is similar to the previous "normal index", except that the value of an indexed column must be unique, but a null value is allowed. If it is a combined index, the combination of the column values must be unique. It has the following ways to create:

(1) CREATE INDEX: Create UNIQUE index indexname on tablename (tablecolumns (length))

(2) Modify table structure: ALTER tablename ADD UNIQUE [IndexName] On (tablecolumns (length))

(3) Specify directly when creating a table: Create TABLE TableName ([...], UNIQUE [IndexName] (tablecolumns (length));