Simple MySQL DataBase Query Method

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. Even if an index is created for queries using the MySQL function, the query speed is similar to that without an index. When using the LIKE xxx % mode, indexing is far faster than without indexing, but the % xxx % mode is the same with indexing and no indexing.