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

Database Query Process

When a client (e.g., a Go or C++ application) issues a SQL statement, MySQL first manages the TCP connection, then the parser checks syntax, the optimizer selects an execution plan (including index choice), and finally the executor calls the storage engine to retrieve 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;

Example query:

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

The parser validates syntax, the optimizer decides which index to use, and the executor invokes the storage engine.

Slow Query Analysis

Enable profiling to see where time is spent:

mysql> set profiling=ON;
mysql> show profiles;

Identify the query ID and view detailed timing: mysql> show profile for 1; Typical bottleneck is the Sending data stage, especially when many rows are returned.

Index‑Related Causes

Use EXPLAIN to see which indexes are chosen and how many rows are scanned. Common issues include:

Scanning many rows or pages.

Low‑selectivity indexes causing full table scans.

Unnecessary index back‑lookup (row‑to‑primary‑key).

Force a specific index when the optimizer picks the wrong one:

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

Insufficient Connection Count

MySQL’s default max_connections is 100 (max 16384). Increase it if many concurrent queries block each other: <code>set global max_connections=500;</ncode> Application‑side connection pools also limit concurrency. For Go’s GORM:

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

Buffer Pool Size

InnoDB’s buffer pool caches data pages in memory. Larger pools increase cache hits and reduce disk I/O.

show global variables like 'innodb_buffer_pool_size';
set global innodb_buffer_pool_size=536870912;  -- 512 MiB

Check the hit rate:

show status like 'Innodb_buffer_pool_%';
-- hit rate = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

A hit rate above 99 % is good; lower values suggest increasing the pool.

Query Cache (Deprecated)

Older MySQL versions had a query cache that could speed up repeated reads, but it was removed after 8.0 because any table update invalidates the cache, making it unsuitable for most workloads.

Summary

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

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

InnoDB buffer pool size and hit rate directly affect I/O; adjust when hit rate drops below ~99 %.

Query cache can help read‑only workloads but is deprecated in modern MySQL.