MySQL database simple query performance analysis

　　Create a data table with such a structure and insert 1.1 million random records to test the query performance.

After 1.1 million random records are inserted, the data table size is 102 MB.

Now we use the SQL queryer that comes with phpMyAdmin For performance analysis.

　　The following is a query test in the indexed mode:

A. Random round-match query of all fields, in 0.0005 (S)

B. Random primary key query in 0.0005 (S)

C. Random query using character functions, with a time of 0.4482 (S)

D. Use the LIKE mode for a query. The query time is 0.0007 (S)

E. Use LIKE Mode 2 for query. The query time is 0.7361 (S)

　　The following example shows how to perform a test when an index is deleted:

A. Random round-match query of all fields, in 0.3439 (S)

B. Random primary key query in 0.0004 (S)

C. Random query using character functions, with a time of 0.4882 (S)

D. Use the LIKE mode for a query. The query time is 0.3487 (S)

E. Use LIKE Mode 2 for query. The query time is 0.7339 (S)

　　Conclusion:

　　When you perform a simple query on a field, if the field is indexed, the query speed is much faster than if no index is created. Use MySQLThe function query speed is similar to that without an index even when an index is created. When using LIKEXxx %In the mode, the index is far faster than the index is not, but % xxx %The mode is the same if there is an index or no index.