Why Is MySQL Query Slow? Hidden Factors and Proven Speed‑Up Tricks

Database Query Process

When a client issues a SQL statement, MySQL goes through connection management, parsing, optimization, execution, and finally the storage engine to retrieve rows.

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT 'name',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT 'age',
  `gender` int(8) NOT NULL DEFAULT '0' COMMENT 'gender',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`),
  KEY `idx_gender` (`gender`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The client (e.g., Go or C++) opens a TCP long‑connection, sends the SQL, and MySQL parses it, checks syntax, then the optimizer chooses an index, and the executor calls the storage engine.

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                |
+----------+------------+---------------------------------------------------+

Use show profile for 1 to get a breakdown of each stage; the "Sending data" phase often dominates when many rows are returned.

Index‑Related Causes

EXPLAIN reveals which index is used and how many rows are scanned.

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

If the optimizer chooses a full table scan, it may be because the index is non‑selective or requires a costly table‑lookup (back‑table).

Index Not as Expected

When many similar indexes exist, MySQL may pick the wrong one. Use FORCE INDEX (idx_age) to force the desired index.

Index Still Slow

Low selectivity (e.g., short prefix indexes) or a large number of matching rows can make an index slower than a full scan.

Connection Count Too Small

MySQL’s default max_connections is 100; increase it with:

mysql> set global max_connections=500;
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+

Application‑side connection pools (e.g., GORM) also need enough connections:

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

Buffer Pool Size

InnoDB’s buffer pool caches data pages; a larger pool improves hit rate.

mysql> show global variables like 'innodb_buffer_pool_size';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_buffer_pool_size  | 134217728 |
+--------------------------+-----------+

Increase it, e.g., to 512 MiB, and monitor hit rate with

show status like 'Innodb_buffer_pool_%';

buffer pool hit rate = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%

Other Tricks

MySQL 8.0 removed the query cache; relying on it is not recommended.

Summary

Slow queries are usually index‑related: wrong index or too many rows.

Insufficient client or server connections limit concurrency; raise max_connections and pool size.

InnoDB buffer‑pool hit rate > 99 % is ideal; increase size if the rate drops.

Query cache can speed up reads but is deprecated and removed in MySQL 8.0.