Why MySQL Indexes Still Slow Queries and How to Fix Them

Root Causes of Slow Queries Even When Indexes Exist

Index design problems : low selectivity, wrong column order (violating the left‑most prefix rule), redundant indexes.

SQL writing issues : SELECT *, LIKE '%pattern', functions or calculations on indexed columns, implicit type conversion.

Data distribution problems : very large tables, severe data skew, hotspot rows.

Table structure issues : oversized columns, inappropriate data types, lack of partitioning.

Database configuration problems : insufficient InnoDB buffer pool, sub‑optimal I/O settings, low max_connections.

Hardware resource bottlenecks : disk I/O saturation, memory shortage, CPU limits.

Detailed Analysis

1. Index Design Problems

Low selectivity – an index on a column that returns a large fraction of rows forces the optimizer to scan many rows. Compute selectivity with:

SELECT COUNT(DISTINCT gender) / COUNT(*) AS selectivity FROM users;

Wrong column order (left‑most prefix) – a composite index can be used only if the query predicates start with the left‑most indexed column.

-- Correct composite index following left‑most rule
CREATE INDEX idx_name_age_gender ON users(name, age, gender);
SELECT * FROM users WHERE name = '张三' AND age = 25;  -- uses index

-- Incorrect: predicate on age only, index not used
SELECT * FROM users WHERE age = 25;  -- full table scan

Redundant indexes – single‑column indexes that are already covered by a composite index waste space and increase write overhead.

-- Redundant single‑column index
CREATE INDEX idx_name ON users(name);
-- Drop it because idx_name_age_gender already covers name
DROP INDEX idx_name ON users;

2. SQL Writing Issues

SELECT * causing back‑table lookups – when a secondary index is used, MySQL must fetch the full row from the clustered index for columns not present in the index.

-- Slow: needs back‑table lookup
SELECT * FROM users WHERE name = '张三';

-- Faster: covering index (only indexed columns are needed)
SELECT name, age FROM users WHERE name = '张三';

LIKE patterns – only a left‑anchored pattern can use an index.

-- Index used (prefix match)
SELECT * FROM users WHERE name LIKE '张%';

-- Index not used (suffix or contains)
SELECT * FROM users WHERE name LIKE '%张%';

Functions on indexed columns – applying a function prevents index usage.

-- No index use
SELECT * FROM users WHERE YEAR(create_time) = 2024;

-- Use range query instead
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';

Implicit type conversion – comparing different data types forces conversion and disables the index.

-- Index lost due to conversion
SELECT * FROM users WHERE user_id = 123;  -- user_id is VARCHAR

-- Correct comparison
SELECT * FROM users WHERE user_id = '123';

3. Data Distribution Issues

Large tables increase I/O even when an index is used. Typical diagnostic query:

SELECT table_name, table_rows,
       ROUND(data_length/1024/1024,2) AS data_size_mb,
       ROUND(index_length/1024/1024,2) AS index_size_mb
FROM information_schema.tables
WHERE table_schema = 'your_database';

Mitigation strategies:

Partition tables by time or range.

Split large tables into multiple shards.

Archive historical data.

Use covering indexes to reduce back‑table reads.

Severe skew can cause the optimizer to choose a full scan despite an index. Example:

SELECT gender, COUNT(*) AS cnt FROM users GROUP BY gender;

If one value dominates, consider not indexing the column or force the index cautiously:

SELECT * FROM users FORCE INDEX(idx_gender) WHERE gender = '男';

4. Table Structure Issues

Oversized columns – large TEXT or BLOB fields increase row size and I/O.

-- Bad: large TEXT column in main table
CREATE TABLE articles (id INT PRIMARY KEY, title VARCHAR(255), content TEXT);

-- Better: split large text to a separate table
CREATE TABLE articles (id INT PRIMARY KEY, title VARCHAR(255));
CREATE TABLE article_contents (article_id INT PRIMARY KEY, content LONGTEXT);

Inappropriate data types – storing numeric data as strings or IP addresses as VARCHAR hurts index efficiency.

-- Bad: IP stored as VARCHAR
CREATE TABLE logs (id INT PRIMARY KEY, ip VARCHAR(15));

-- Good: store as unsigned INT using INET_ATON/INET_NTOA
CREATE TABLE logs (id INT PRIMARY KEY, ip INT UNSIGNED);
INSERT INTO logs (ip) VALUES (INET_ATON('192.168.1.1'));
SELECT INET_NTOA(ip) FROM logs;

5. Database Configuration Issues

Key InnoDB variables to check and tune:

-- Buffer pool (set to 70‑80% of RAM)
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SET GLOBAL innodb_buffer_pool_size = 12884901888;  -- 12 GB on a 16 GB server

-- I/O capacity and thread counts
SHOW VARIABLES LIKE 'innodb_io_capacity';
SHOW VARIABLES LIKE 'innodb_read_io_threads';
SHOW VARIABLES LIKE 'innodb_write_io_threads';
SHOW VARIABLES LIKE 'max_connections';

6. Using EXPLAIN for Execution‑Plan Analysis

EXPLAIN SELECT * FROM users WHERE name = '张三';
-- Important columns in the output:
-- type  (ALL, index, range, ref, const) – avoid ALL
-- key   – actual index used
-- rows  – estimated rows scanned (lower is better)
-- Extra – look for "Using filesort" or "Using temporary" which are performance killers

Four‑Step Troubleshooting Checklist

Enable the slow‑query log and identify problematic statements.

Run EXPLAIN to view the execution plan.

Inspect index design, SQL syntax, data distribution, table schema, and DB configuration.

Apply optimizations: redesign indexes (respect left‑most prefix, drop redundancies), create covering indexes, adjust schema (split large columns, use proper data types), partition or shard large tables, and tune hardware/configuration parameters.