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

1. Database Query Process

A SQL statement goes through several stages: the client (written in Go, C++, etc.) opens a TCP connection, sends the query to MySQL, which first parses the statement for syntax errors, then the optimizer selects an index, and finally the executor calls the storage engine to fetch rows.

The storage engine (InnoDB by default) uses a buffer pool of 16 KB pages to cache data and index pages in memory, reducing disk I/O.

2. Slow Query Analysis

Enable profiling to see where time is spent:

mysql> set profiling=ON;
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+

After running a query, list recorded statements:

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

Inspect a specific query:

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.000021 |
| query end            | 0.000015 |
| closing tables       | 0.000014 |
| freeing items        | 0.000047 |
| cleaning up          | 0.000027 |
+----------------------+----------+

The dominant cost is often the Sending data phase, which indicates the executor is reading many rows.

3. Index‑Related Causes

Use EXPLAIN to see which index is chosen and how many rows are scanned:

EXPLAIN SELECT * FROM user WHERE age>=60;

If the type column shows ALL, a full table scan occurs. Even when an index exists (e.g., idx_age), MySQL may ignore it if the index is non‑unique and requires a costly back‑lookup to the primary key.

Common index pitfalls:

Low selectivity (many rows share the same indexed value).

Prefix indexes that are too short.

Implicit type conversion or inequality operators that prevent index use.

Force the desired index when necessary:

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

4. Insufficient Connections

MySQL’s default max_connections is 100 (max 16384). Increase it when the server side limits concurrency:

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

Application‑side connection pools (e.g., Go’s gorm) also need enough slots:

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

5. Buffer Pool Too Small

The InnoDB buffer pool caches data and index pages. A larger pool reduces disk reads. Check its size:

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

Increase it (e.g., to 512 MB):

mysql> set global innodb_buffer_pool_size = 536870912;
mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 536870912 |
+-------------------------+-----------+

Monitor hit rate:

mysql> show status like 'Innodb_buffer_pool_%';

Hit rate = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%. A rate above 99% is typical; lower values suggest enlarging the pool.

6. Other Tricks

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

7. Summary

Slow queries are often index‑related: wrong index, low selectivity, or massive row scans.

Too few client‑server connections limit concurrency; raise max_connections and enlarge application connection pools.

InnoDB buffer pool size and hit rate directly affect I/O; increase the pool if hit rate drops below ~99%.

Query cache can speed up read‑only workloads but is deprecated in modern MySQL versions.