Why MySQL Queries Slow Down and How to Speed Them Up

MySQL Query Execution Flow

When a client (e.g., a Go or C++ application) sends a SQL statement, MySQL first establishes a TCP long‑connection, which is managed by the connection manager. The parser checks the syntax, the optimizer selects an appropriate index, the executor invokes the storage engine, and the storage engine reads data pages, often from the InnoDB buffer pool that caches 16 KB pages in memory.

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '名字',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `gender` int(8) NOT NULL DEFAULT '0' COMMENT '性别',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`),
  KEY `idx_gender` (`gender`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The client sends the SQL (e.g., SELECT * FROM user WHERE gender = 1 AND age = 100;) to MySQL, which parses it, optimizes it, and finally returns the result rows.

Slow‑Query Analysis

Enable profiling to see where time is spent:

mysql> set profiling=ON;
mysql> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.06811025 | select * from user where age>=60                 |
|        2 | 0.00151375 | select * from user where gender=2 and age=80      |
|        3 | 0.00230425 | select * from user where gender=2 and age=60      |
|        4 | 0.00070400 | select * from user where gender=2 and age=100     |
|        5 | 0.07797650 | select * from user where age!=60                  |
+----------+------------+---------------------------------------------------+

Inspect a specific query with SHOW PROFILE FOR QUERY 1; to see stages such as Sending data , which often dominates the runtime when many rows are returned.

Index‑Related Issues

Use EXPLAIN to see which index is chosen and how many rows are scanned. Common problems include full‑table scans (type=ALL), low index selectivity, and the need for a table‑lookup (回表) when a non‑primary index is used.

EXPLAIN SELECT * FROM user WHERE age>=60;
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | user  | ALL  | idx_age       | NULL | NULL    | NULL | 10000 |   100.00 | NULL        |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------+-------------+

If the optimizer picks a non‑optimal index, you can force the desired one:

SELECT * FROM user FORCE INDEX (idx_age) WHERE age>=60;

Connection Count Too Small

MySQL’s default max_connections is 100 (max 16384). Increasing it allows more concurrent queries:

mysql> set global max_connections=500;

Application‑side connection pools (e.g., GORM) also need to be sized appropriately:

func Init() {
    db, _ := gorm.Open(mysql.Open(conn), config)
    sqlDB, _ := db.DB()
    sqlDB.SetMaxIdleConns(200)   // idle connections
    sqlDB.SetMaxOpenConns(1000)  // max open connections
}

InnoDB Buffer Pool Size

The buffer pool caches data pages in memory. Its hit rate can be checked with:

show status like 'Innodb_buffer_pool_%';

Hit rate = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests). A rate above 99 % is good; lower rates suggest enlarging the pool:

mysql> set global innodb_buffer_pool_size=536870912;  -- 512 MB

Other Tricks

MySQL 8.0 removed the query cache because it invalidates on any table change, making it unsuitable for frequently updated data.

Summary

Slow queries are often caused by index problems—either the wrong index is chosen or the query touches too many rows.

Insufficient client‑side or server‑side connection limits throttle concurrency; raising max_connections and enlarging the application pool helps.

The InnoDB buffer pool speeds up data access; monitor its hit rate and increase its size if the rate falls below ~99 %.

Query cache can improve performance for static data but is deprecated in MySQL 8.0 and generally not recommended.