Understanding MySQL Query Performance: Causes of Slow Queries and Optimization Techniques

Database Query Process

A SQL statement goes through several stages: the client (e.g., a Go or C++ program) opens a TCP connection to MySQL, the server receives the statement, the parser checks syntax, the optimizer chooses an execution plan and appropriate indexes, the executor calls the storage engine, and the result rows are sent back to the client.

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;

When the client executes a query such as:

SELECT * FROM user WHERE gender = 1 AND age = 100;

the statement is parsed for syntax errors, optimized to select the best index, and then executed by the storage engine (InnoDB by default).

Slow Query Analysis

Enabling profiling helps locate bottlenecks: SET profiling = ON; After running the query, you can view recorded profiles: SHOW PROFILES; To see detailed timing for a specific query ID: SHOW PROFILE FOR 1; The "Sending data" stage often dominates the time, indicating that the executor is reading large result sets.

Index‑Related Issues

Using EXPLAIN reveals which index (if any) MySQL chooses. A type=ALL means a full table scan. Even when an index is available, MySQL may ignore it if the estimated row count is high or if the index requires a costly back‑table lookup.

Common problems include:

Low index selectivity (e.g., short prefix indexes on URLs).

Large number of rows matching the index, leading to high rows estimates.

Implicit type conversions or inequality operators that cause index loss.

Forcing a specific index can be done with FORCE INDEX(idx_age), and the effect can be verified with EXPLAIN.

Connection Limits

Both the MySQL server and the application client maintain connection pools. If the total number of concurrent connections is too small, queries queue and appear slow. Increase the server’s maximum connections: SET GLOBAL max_connections = 500; In the application (e.g., using GORM in Go), adjust the pool size:

db, _ := gorm.Open(mysql.Open(dsn), &config{})
sqlDB, _ := db.DB()
sqlDB.SetMaxIdleConns(200)   // idle connections
sqlDB.SetMaxOpenConns(1000)  // maximum open connections

InnoDB Buffer Pool Tuning

The InnoDB buffer pool caches data pages in memory. A larger pool reduces disk I/O. Check the current size:

SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

Increase it if the hit rate is low (below ~99%):

SET GLOBAL innodb_buffer_pool_size = 536870912;  -- 512 MiB

Monitor hit rate with: SHOW STATUS LIKE 'Innodb_buffer_pool_%'; Hit rate = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%.

Query Cache (Deprecated)

MySQL 8.0 removed the query cache because it invalidates on any table change, making it unsuitable for most workloads.

Summary

Slow queries are usually caused by index problems or excessive row scans.

Insufficient client or server connections limit concurrency; increase them to improve throughput.

InnoDB buffer pool size and hit rate affect I/O; tune the pool when hit rate drops below 99%.

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