Introduction to three methods to optimize MySQL database queries

Any database programmer has the following experience: a bad SQL query statement in a high-traffic database driver can seriously affect the running of the entire application, it not only consumes more database time, but also affects other application components. Like other disciplines, optimizing query performance is largely determined by developers' direct query performance.

Any database programmer has the following experience: a bad SQL query statement in a high-traffic database driver can seriously affect the running of the entire application, it not only consumes more database time, but also affects other application components. Like other disciplines, optimizing query performance is largely determined by developers' direct query performance.

Any database programmer has the following experience: a bad SQL query statement in a high-traffic database driver can seriously affect the running of the entire application, it not only consumes more database time, but also affects other application components.

Like other disciplines, optimizing query performance is largely dependent on developers' intuition. Fortunately, databases like MySQL come with some assistance tools. This article briefly discusses the three tools: Using indexes, analyzing queries using EXPLAIN, and adjusting the internal configuration of MySQL.

I. Using Indexes

MySQL allows you to index database tables so that you can quickly search for records without scanning the entire table at the beginning, which significantly speeds up query. Each table can have up to 16 indexes. In addition, MySQL also supports multiple column indexes and full-text searches.

It is very easy to add an INDEX to a table. You only need to call the create index Command and specify its fields for the INDEX. List A provides 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

Here, the username field of the users table is indexed to ensure that the SELECT query statements that reference this field in the WHERE or HAVING clause run faster than those that do not have an index added. You can run the show index Command to check whether the INDEX has been created (List B ).

498) this. width = 498; 'onmousewheel = 'javascript: return big (this) 'height = 146 alt = "" src = "/files/uploadimg/20081219/1447590 .png" width = 522 border = 0>
List B

It is worth noting that indexes are like a double-edged sword. Indexing each field of a table is usually unnecessary and may slow down the operation because MySQL has to re-create indexes for these additional tasks every time it inserts or modifies data in the table. On the other hand, it is not a good idea to avoid indexing each field of a table, because the query operation slows down when the record insertion speed is increased. This requires a balance. For example, when designing an index system, it is wise to consider the table's main functions (data repair and editing.

Ii. Optimize Query Performance

When analyzing query performance, it is also useful to consider the EXPLAIN keyword. The EXPLAIN keyword is generally placed before the SELECT query statement to describe how MySQL performs the query operation, and the number of rows to be executed in the result set returned by MySQL. The following simple example illustrates the process (List C:

498) this. width = 498; 'onmousewheel = 'javascript: return big (this) 'height = 149 alt = "" src = "/files/uploadimg/20081219/1447591 .png" width = 529 border = 0>
List C

The query is based on two table connections. The EXPLAIN keyword describes how MySQL processes the connection between the two tables. It must be clear that the current design requires MySQL to process one record in the country table and the entire 4019 record in the city table. This means that other optimization techniques can be used to improve the query method. For example, add the following index (List D) to the city table ):

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-use the EXPLAIN keyword for query, we can see a significant improvement (List E): 498) this. width = 498; 'onmousewheel = 'javascript: return big (this) 'height = 170 alt = "" src = "/files/uploadimg/20081219/1447592 .png" width = 542 border = 0>
List E

In this example, MySQL only needs to scan 333 records in the city table to generate a result set, and the number of scan records is almost reduced by 90%! Naturally, database resources are faster to query and more efficient.

3. Adjust internal variables

MySQL is so open that you can easily adjust its default settings to achieve better performance and stability. Key variables to be optimized are as follows:

Change the index buffer length (key_buffer)

Generally, this variable controls the buffer length to be used when processing index tables (read/write operations. MySQL user manual points out that this variable can be continuously increased to ensure the optimal performance of the index table, and it is recommended to use 25% of the size of the system memory as the value of this variable. This is one of the most important configuration variables of MySQL. If you are interested in Optimizing and improving the system, you can change the value of the key_buffer_size variable.

Change the table length (read_buffer_size)

When a query continuously scans a table, MySQL allocates a memory buffer for it. The read_buffer_size variable controls the size of the buffer. If you think continuous scanning is too slow, you can increase the performance by increasing the variable value and memory buffer size.

Set the maximum number of opened tables (table_cache)

This variable controls the maximum number of tables opened by MySQL at any time, thereby controlling the server's ability to respond to input requests. It is closely related to the max_connections variable. Increasing the table_cache value enables MySQL to open more tables, just like increasing the max_connections value to increase the number of connections. When receiving a large number of requests from different databases and tables, consider changing the size of this value.

Set a time limit for slow query (long_query_time)

MySQL has a "Slow query log", which automatically records all queries that have not been completed within a specific time range. This log is useful for tracking inefficient or misperforming queries and searching for optimization objects. The long_query_time variable controls the maximum time limit, in seconds.

The above discussion provides the usage methods of the three tools used to analyze and optimize SQL queries to improve the performance of your applications. Use them to optimize them happily!