MySQL Cost Evaluation, Index Pitfalls, and Memory Fragmentation in Slow Query Optimization

1. Background

At the beginning of the year the team launched a "Service Stability Issue Governance" project, focusing on error logs, slow SQL, and interface performance to improve system reliability. This article uses real slow‑SQL cases to analyze underlying principles and share observations.

2. How MySQL Evaluates Cost

A sudden alert revealed a slow SQL statement. The author first ran EXPLAIN , which returned a plan in milliseconds, showing a full‑table scan ( key=null ). MySQL’s optimizer selects the plan with the lowest estimated cost, which consists of I/O cost and CPU cost.

The I/O cost is calculated as pages × io_block_read_cost (default 0.25) + 1.0 , and the CPU cost as rows × cpu_tuple_cost (default 0.1) . Total cost = I/O cost + CPU cost. Because the scan is sequential, the default io_block_read_cost is 0.25, not 1.0.

IO cost: pages × io_block_read_cost (default 0.25) + 1.0
CPU cost: rows × cpu_tuple_cost (default 0.1)
Total cost = IO cost + CPU cost

3. Adding an Index Still Doesn’t Help

Even after creating an index on column_key , the production environment still showed slow queries while the test environment could use the index. This occurs when the column’s selectivity is low; the optimizer deems the index lookup costlier than a full scan. In test data with fewer rows, the index appears beneficial.

Guideline: avoid creating separate indexes on columns whose distinct‑value ratio is below 10 %; place high‑selectivity columns first in composite indexes.

SELECT COUNT(DISTINCT column_name) / COUNT(*)

4. Memory Fragmentation Matters

When memory fragmentation is severe, statistical information may become outdated, causing the optimizer’s cost estimates to diverge from reality. Fragmentation inflates physical I/O because related rows are spread across many pages, reducing buffer‑pool hit rates.

Example: a table with 2 billion obsolete rows was cleaned, but SHOW TABLE STATUS LIKE 'xxx_price' revealed Data_free=54835281920 , indicating large fragmentation.

SHOW TABLE STATUS LIKE 'xxx_price'

5. Pitfalls of Prefix Indexes

Using a prefix index in a unique constraint can cause false duplicate conflicts because only the prefix is stored in the leaf nodes. For long string columns, prefix indexes save space, but uniqueness must be guaranteed for the prefix.

UNIQUE (cate_id, brand_id,
        model_id, key_props(10))

6. Index Merge

MySQL can combine multiple single‑column indexes to satisfy a query, reducing the need for a full table lookup. For example, with separate indexes on k1 and k2 , MySQL retrieves matching row IDs from each index, intersects them, and then accesses the rows.

SELECT * FROM xxx_supplier_order
WHERE k1 = '123' AND k2 = '345'

Benefits include fewer row lookups and ordered access, which lowers random I/O.

7. Slow Queries Even When SQL Looks Fine

Sometimes the SQL itself is not the bottleneck; resource pressure on the MySQL instance (disk, CPU, network) can cause latency. Heavy data ingestion from a big‑data platform, long‑running transactions that inflate the undo log, or a bloated buffer pool can all degrade performance. Monitoring SHOW ENGINE INNODB STATUS for a rising History list length helps identify such issues.

8. Conclusion

The article presented several real‑world cases to illustrate MySQL’s cost estimation, index design pitfalls, memory fragmentation, and resource‑related slow‑query causes. Understanding these mechanisms enables more effective slow‑SQL governance.