A simple query method for MySQL database

Create a data table of this structure and insert 1.1 million random records for query performance testing.

After inserting 1.1 million random records, the size of the datasheet is 102MB.

Now use phpMyAdmin's own SQL query to perform profiling.

The following is a query test with indexed mode:

A. Random character field full-match query, spents 0.0005 (S)

B. Random primary key query, spents 0.0005 (S)

C. Random use of character function queries, spents 0.4482 (S)

D. Use like mode one query, spents 0.0007 (S)

E. Using the like mode two query, spents 0.7361 (S)

The following is a test that deletes an index:

A. Random character field full-match query, spents 0.3439 (S)

B. Random primary key query, spents 0.0004 (S)

C. Random use of character function queries, spents 0.4882 (S)

D. Use like mode one query, spents 0.3487 (S)

E. Using the like mode two query, spents 0.7339 (S)

Conclusion:

In the case of a simple query on a field, if the field is indexed, the query speed is much faster than if no index was built. Queries that use the MySQL function still have the same speed as if they were indexed. When using the like xxx% mode, the index is much faster than the index, but the%xxx% mode is indexed and not indexed.