Why Is MySQL Query Slow? Hidden Factors Beyond Indexes and How to Fix Them

Database Query Process

When a client issues a SQL statement, MySQL goes through several stages: the parser checks syntax, the optimizer selects an execution plan (often choosing an index), the executor calls the storage engine, and the storage engine retrieves the data.

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., a Go or C++ application) opens a TCP long‑connection to MySQL, which is managed by MySQL's connection management module.

Slow Query Analysis

Enable profiling to see where time is spent:

mysql> set profiling=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

After running a query, view the recorded profiles:

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's detailed timing:

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000074 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000034 |
| init                 | 0.000032 |
| System lock          | 0.000027 |
| optimizing           | 0.000020 |
| statistics           | 0.000058 |
| preparing            | 0.000018 |
| executing            | 0.000013 |
| Sending data         | 0.067701 |
| end                  | 0.000015 |
| closing tables       | 0.000014 |
| freeing items        | 0.000047 |
| cleaning up          | 0.000027 |
+----------------------+----------+

The Sending data stage often dominates when many rows are returned.

Index‑Related Causes

Use EXPLAIN to see which indexes are used and how many rows are scanned.

How many rows ( rows ) need to be scanned?

How many 16 KB pages must be read?

Does the plan require a table‑row lookup (back‑table) or can it use the primary key directly?

If an index is not used, you can force it:

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

Even when an index is chosen, performance may suffer due to low selectivity (e.g., prefix indexes on URLs) or because the index still returns many rows, causing large rows estimates.

Insufficient Connection Count

MySQL’s default max_connections is 100 (up to 16384). Increase it when the server‑side limit is too low:

mysql> set global max_connections=500;
Query OK, 0 rows affected (0.00 sec)

Application‑side connection pools also limit concurrency. For example, in Go using GORM:

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

InnoDB Buffer Pool Size

The buffer pool caches data and index pages in memory. A larger pool improves hit rates.

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

Increase it if the hit rate falls below 99%:

mysql> set global innodb_buffer_pool_size=536870912;
Query OK, 0 rows affected (0.01 sec)

Calculate the hit rate:

buffer_pool_hit_rate = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%

Other Tricks

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

Summary

Slow queries are often caused by index problems—wrong index choice or scanning too many rows.

Too few client or server connections limit concurrency; increasing connection limits speeds up execution.

InnoDB buffer pool size affects performance; keep hit rate >99% or enlarge the pool.

Query cache can speed up reads but is deprecated in MySQL 8.0 and generally not recommended.