Brief introduction of three methods to optimize MySQL database query

In an optimized query, database applications (such as MySQL) mean the operation and use of tools. The purpose of optimizing queries is to use indexes, analyze queries using explain, and adjust the internal configuration of MySQL.

Any database programmer would have this experience: in a high traffic database driver, a bad SQL query statement can have a serious impact on the operation of the entire application, not only consume more database time, and it will affect other application components.

As with other disciplines, optimizing query performance largely depends on the developer's intuition. Fortunately, databases like MySQL have a few help tools on their own. This article briefly discusses the three kinds of tools: Using indexes, using explain to analyze queries, and adjusting the internal configuration of MySQL.

1. Use index

MySQL allows you to index database tables so that you can quickly find records without having to scan the entire table in the first place, thereby dramatically speeding up the query. Each table can have up to 16 indexes, and MySQL also supports multiple-column indexes and Full-text searches.

Adding an index to a table is very simple, just call a CREATE INDEX command and specify its domain for the index. List A gives an example:

mysql> CREATE INDEX idx_username ON users(username);
Query OK, 1 row affected (0.15 sec)
Records: 1　Duplicates: 0　Warnings: 0

List A

Here, the username domain of the users table is indexed to make sure that the SELECT query statement referencing this field in the WHERE or HAVING clause runs faster than if no index was added. The show index command lets you see that the index has been created (List B).

List B

It's worth noting that the index is like a double-edged sword. Indexing each field in a table is often unnecessary and is likely to slow down because MySQL has to index the extra work each time it inserts or modifies data into the table. On the other hand, it is also not a good idea to avoid indexing each field in a table, because the query operation slows down when you increase the speed at which you insert records. This requires finding a balance, for example, when designing an indexing system, it is a wise choice to consider the main function of the table (data repair and editing).

2. Optimize query performance

When analyzing query performance, it is also useful to consider the explain keyword. The Explain keyword is typically placed in front of a SELECT query statement to describe how MySQL performs a query operation and the number of rows that MySQL needs to perform to successfully return the result set. The following is a simple example to illustrate the process of (list C):

List C

Here the query is based on two table connections. The Explain keyword describes how MySQL handles connections between the two tables. It must be clear that the current design requires MySQL to process a record in the country table and a total of 4,019 records in the city table. This means that you can also use other optimization techniques to improve its query methods. For example, add the following index to the city table (list D):

mysql> CREATE INDEX idx_ccode ON city(countrycode);
Query OK, 4079 rows affected (0.15 sec)
Records: 4079　Duplicates: 0　Warnings: 0

List D

Now, when we re using the Explain keyword to query, we can see a significant improvement (list E):

List E

In this example, MySQL now only needs to scan the city table 333 records to produce a result set, the number of scan records almost reduced by 90%! Naturally, database resources are queried faster and more efficiently.