Why MySQL Queries Slow Down and How to Speed Them Up

I have been using Ctrl+C and Ctrl+V to develop CRUD code for years. MySQL queries can be slow, a frequent interview question.

Besides indexes, many factors can cause database query slowdown.

Database Query Process

When a query is issued, the client (e.g., Go or C++) opens a TCP long‑connection to MySQL. The connection manager handles the link, the parser checks syntax, the optimizer chooses an index, the executor calls the storage engine, and the result is returned.

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 a SQL statement, e.g.:

select * from user where gender = 1 and age = 100;

If the syntax is wrong, the parser reports an error.

The optimizer selects an index based on rules, then the executor invokes the storage engine.

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, use show profiles; and show profile for query_id; to view detailed timing, e.g., the "Sending data" stage often dominates.

Index‑Related Reasons

Use EXPLAIN to see which indexes are used and how many rows are scanned. Factors include rows scanned, 16KB pages read, and whether a primary‑key lookup avoids a table‑row lookup.

Example:

explain select * from user where age>=60;

The query performs a full table scan because the optimizer chose no index.

Common index issues:

Scanning too many rows.

Insufficient index selectivity (e.g., low‑cardinality prefixes).

Need to force a specific index with FORCE INDEX.

Connection Count Too Low

MySQL’s server layer manages client connections. With only one connection, concurrent queries block each other, causing apparent long runtimes.

Increase the number of connections on both the database side ( max_connections) and the application side (connection pool size).

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

In Go with 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)
}

Buffer Pool Too Small

InnoDB uses a memory buffer pool to cache data pages. A larger pool improves cache hit rate and reduces disk I/O.

Check the current size:

mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.01 sec)

Increase it:

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

Monitor hit rate with:

show status like 'Innodb_buffer_pool_%';

Hit rate = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%.

If the hit rate falls below 99%, consider enlarging the pool.

Other Tricks

MySQL used to have a query cache that could speed up repeated reads, but it was removed after version 8.0 because any table update invalidates the cache.

Summary

Slow queries are often index‑related; wrong or missing indexes cause full scans.

Insufficient client or server connections limit concurrency; increase connection limits and pool sizes.

InnoDB buffer pool size affects cache hit rate; enlarge it if hit rate < 99%.

Query cache can improve speed but is deprecated in MySQL 8.0 and later.