Optimize the MySQL database query Solution

The following articles mainly describe three useful solutions for optimizing MySQL database queries, including how to correctly use related indexes and how to optimize query performance, the following describes how to optimize the query performance. In optimizing queries, database applications (such as MySQL (the best combination with PHP) mean

The following articles mainly describe three useful solutions for optimizing MySQL database queries, including how to correctly use related indexes and how to optimize query performance, the following describes how to optimize the query performance. In optimizing queries, database applications (such as MySQL (the best combination with PHP) mean

The following articles mainly describe three useful solutions for optimizing MySQL database queries, including how to correctly use related indexes and how to optimize query performance, the following describes how to optimize the query performance.

In optimizing queries, database applications (such as MySQL (the best combination with PHP) mean the operation and use of tools. Using indexes, using EXPLAIN to analyze queries, and adjusting the internal configuration of MySQL (the best combination with PHP) can optimize 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 dependent on developers' intuition. Fortunately, databases like MySQL (the best combination with PHP) come with some assistance tools. This article briefly discusses the three tools: Using indexes, using EXPLAIN to analyze queries, and adjusting the internal configurations of MySQL (the best combination of PHP and MySQL.

Three methods to optimize MySQL database query 1: Using Indexes

MySQL (the best combination with PHP) allows you to index database tables to 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 (the best combination with PHP) also supports multi-column indexing and full-text retrieval.

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:

List

 
 

  
  
MySQL (best combination with PHP)> create index idx_username ON users (username );
  
  
Query OK, 1 row affected (0.15 sec)
  
  
Records: 1 Duplicates: 0 Warnings: 0
 
 

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 ).

List B

 
 

  
  
MySQL (best combination with PHP)> show index from users;
  
  
-------------- + ------------- + ----------- + ------------- + ---------- + -------- + -------------- + --------- +
  
  
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
  
  
-------------- + ------------- + ----------- + ------------- + ---------- + -------- + -------------- + --------- +
  
  
| Users | 1 | idx_username | 1 | username | A | NULL | YES | BTREE |
  
  
-------------- + ------------- + ----------- + ------------- + ---------- + -------- + -------------- + --------- +
  
  
1 row in set (0.00 sec)
 
 

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 (the best combination with PHP) is used to insert or modify data into the table) you have to re-create an index for this extra job each time. 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.

Three methods to optimize MySQL database query 2: Optimize Query Performance

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

List C

 
 

  
  
MySQL (best combination with PHP)> explain select city. name, city. district FROM city, country WHERE city. countrycode = country. code AND country. code = 'ind ';
  
  
+ ---- + ------------- + --------- + ------- + --------------- + --------- + ------- + ------ + ------------- +
  
  
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  
  
+ ---- + ------------- + --------- + ------- + --------------- + --------- + ------- + ------ + ------------- +
  
  
| 1 | SIMPLE | country | const | Prima (the most comprehensive virtual host Management System) RY | Prima (the most comprehensive virtual host Management System) RY | 3 | const | 1 | Using index |
  
  
| 1 | SIMPLE | city | ALL | NULL | 4079 | Using where |
  
  
+ ---- + ------------- + --------- + ------- + --------------- + --------- + ------- + ------ + ------------- +
 
 

2 rows in set (0.00 sec) the query here is based on two table connections. The EXPLAIN keyword describes how MySQL (the best combination with PHP) processes the two tables connected. It must be clear that the current design requires MySQL (the best combination with PHP) 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 ):

List D

 
 

  
  
MySQL (the best combination with PHP)> create index idx_ccode ON city (countrycode );
  
  
Query OK, 4079 rows affected (0.15 sec)
  
  
Records: 4079 Duplicates: 0 Warnings: 0
 
 

Now, when we re-use the EXPLAIN keyword for queries, we can see a significant improvement (List E ):

List E

 
 

  
  
MySQL (best combination with PHP)> explain select city. name, city. district FROM city, country WHERE city. countrycode = country. code AND country. code = 'ind ';
  
  
+ ---- + ------------- + --------- + ------- + --------------- + ----------- + --------- + ------- + ------ + ------------- +
  
  
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  
  
+ ---- + ------------- + --------- + ------- + --------------- + ----------- + --------- + ------- + ------ + ------------- +
  
  
| 1 | SIMPLE | country | const | Prima (the most comprehensive virtual host Management System) RY | Prima (the most comprehensive virtual host Management System) RY | 3 | const | 1 | Using index |
  
  
| 1 | SIMPLE | city | ref | idx_ccode | 3 | const | 333 | Using where |
  
  
+ ---- + ------------- + --------- + ------- + --------------- + ----------- + --------- + ------- + ------ + ------------- +
  
  
2 rows in set (0.01 sec)
 
 

In this example, MySQL (the best combination with PHP) now 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.

Three methods to optimize MySQL database query 3: adjust internal variables

MySQL (the best combination with PHP) 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. The MySQL (best combination with PHP) User Manual states that this variable can be continuously increased to ensure the optimal performance of the index table, and it is recommended to use the 25% size with the system memory as the value of this variable. This is one of the most important configuration variables for MySQL (the best combination with PHP). 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 (the best combination with PHP) 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 opened tables in MySQL (the best combination with PHP) at any time, thus 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 (the best combination with PHP) 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 (the best combination with PHP) carries 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!