Solutions to slow execution of MySQL query include optimizing SQL statements, using indexes reasonably, adjusting configuration parameters, and other detailed optimizations. 1. Optimize SQL: Avoid SELECT *, use LIMIT to reduce the amount of data, simplify JOIN, and do not operate field functions in WHERE; 2. Use index: Create indexes for commonly used query fields, pay attention to combining index order, avoid excessive indexes and analyze tables regularly; 3. Adjust configuration: Set the appropriate buffer pool size, enable slow query logs, appropriately increase the number of connections, and check the use of temporary tables; 4. Other optimizations: Reasonably design the table structure, consider partitioning tables, handle lock waiting issues, and maintain tables regularly.

MySQL query execution is a common problem that many developers and operation and maintenance personnel encounter. It may be caused by multiple factors, such as missing indexes, irregular query statements, improper server configuration, etc. To solve this problem, we cannot rely solely on a single method, but we need to start from multiple angles to investigate.

The following are some common reasons and corresponding optimization suggestions:

Check and optimize SQL query statements

If SQL is not well written, it is often the source of performance bottlenecks. Even if the data volume is small, complex subqueries, no limit on the number of rows returned, or frequent use of SELECT * can cause the query to slow down.

• **Avoid SELECT ***: Only take the fields you need to reduce unnecessary data transfer.
• Reasonable use of LIMIT : Especially when doing pagination queries, adding LIMIT can greatly reduce the amount of data scanned by the database.
• Reduce the complexity of JOINs : Try to avoid multi-layer nested JOINs, or make sure that the JOIN fields have indexes.
• Avoid performing function operations on fields in WHERE : For example, WHERE YEAR(create_time) = 2023 , which will cause the index to fail.

A simple example:
What you wrote is:

 SELECT * FROM orders WHERE YEAR(created_at) = 2023;

It's better to change it to:

 SELECT id, user_id, amount FROM orders WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

Make sure that the relevant fields have appropriate indexes

For tables without indexes, querying is a full table scan, which is inefficient. But it is not necessarily fast after adding an index, it depends on how to use it.

• Create indexes for frequently queried columns : such as primary keys, foreign keys, status fields, etc.
• Pay attention to the order of combined indexes : compound indexes follow the principle of leftmost matching, such as (user_id, status) . If you only use status to query, the index will not take effect.
• Avoid too many indexes : Indexes can take up disk space and affect write speed.
• Periodic analysis table : Use ANALYZE TABLE to update statistics to help the optimizer choose a better execution plan.

You can view the execution plan to determine whether the index hits:

 EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

Analyze and optimize MySQL configuration parameters

Sometimes the slow query is not a problem with SQL or index, but the configuration of MySQL itself is unreasonable.

• Adjust the buffer pool size (innodb_buffer_pool_size) : This is a key parameter that affects performance. It is generally recommended to set it to 50%~80% of physical memory.
• Turn on slow query logs : find out the really time-consuming queries.

   slow_query_log = 1
  long_query_time = 1
  slow_query_log_file = /var/log/mysql/slow.log

• Increase the number of connections appropriately (max_connections) : If your application has high concurrency, insufficient connections will also slow down the response time.
• Check the usage of temporary tables regularly : If you find a large number of temporary disk tables, it means that the sorting or group by operation is too heavy, and you need to optimize SQL or increase tmp_table_size.

---

Other small details that are easily overlooked

• The table structure design is unreasonable : for example, the field type is too large (such as using TEXT to store short strings), which will also affect performance.
• Partitioning and table division strategy : Under the large amount of data, the performance of a single table has significantly decreased, so you can consider making table divisions according to time or business logic.
• Lock waiting problem : Long transactions or uncommitted write operations may cause blockage. You can use SHOW ENGINE INNODB STATUS to view the current lock situation.
• Regular table maintenance : For example, OPTIMIZE TABLE can organize fragments and improve query efficiency.

---

In general, the problem of slow MySQL query optimization requires starting from SQL itself, index settings, configuration tuning and other aspects. Some problems can be effective by simply adjusting them, while others require continuous observation and iteration. Basically, there are only these directions, let’s try it out.

The above is the detailed content of Troubleshooting slow query execution times in MySQL. For more information, please follow other related articles on the PHP Chinese website!