Why Is MySQL Slow? Beyond Index Issues and Practical Fixes

Database Query Flow

A SQL statement is sent from a client (e.g., Go or C++) over a TCP connection to MySQL. The server’s connection manager handles the link, then the parser checks syntax, the optimizer selects an index, and the executor calls the storage engine (InnoDB) to fetch rows.

Slow‑Query Analysis

Enable profiling with set profiling=ON and run the query. show profiles lists each query’s duration; show profile for <query_id> breaks down the time spent in stages such as starting, checking permissions, optimizing, and especially Sending data, which often dominates when many rows are returned.

Index‑Related Causes

Using EXPLAIN reveals which index is chosen, rows scanned, and whether a table scan occurs. Common problems include:

Choosing an index that still requires reading many rows and a costly back‑table lookup.

Index selectivity too low (e.g., short prefix indexes on URLs).

Large rows estimate indicating many matching rows.

When the optimizer picks a full table scan, it is often because the estimated row count is high and the index would require extra lookups.

Solutions:

Force a specific index with FORCE INDEX (idx_age) and verify with EXPLAIN.

Improve index selectivity or shorten result sets with LIMIT or batch fetching (order by id, track max id).

Insufficient Connection Count

MySQL’s server connection manager limits concurrency. With only one connection, later queries wait for earlier ones to finish, causing apparent minutes‑long delays.

Increase the server‑side limit via set global max_connections=500 (default max is 100, max possible 16384). On the client side, use a connection pool; for example, in Go with GORM:

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

InnoDB Buffer Pool Size

InnoDB caches data pages in a buffer pool (default 128 MiB). A larger pool increases the chance of hitting memory instead of disk I/O. Check size with show global variables like 'innodb_buffer_pool_size' and adjust, e.g., set global innodb_buffer_pool_size=536870912 to raise it to 512 MiB.

Measure hit rate via show status like 'Innodb_buffer_pool_%'. Hit rate = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests). A rate below 99 % suggests increasing the pool.

Query Cache (Deprecated)

MySQL 8.0 removed the query cache because any table update invalidates the entire cache, making it unsuitable for frequently changing data.

Summary

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

Too few client or server connections limit concurrency; raise max_connections and configure a larger connection pool.

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

Query cache can speed up read‑only workloads but is removed in MySQL 8.0 and generally not recommended.