7 Practical Slow SQL Optimization Techniques to Boost Query Performance 100×

1. Introduction

Rapid data growth and traffic increase cause slow SQL, leading to long page loads, API delays, timeouts, or crashes. Causes include poor index design, inefficient queries, complex schemas, and large data volumes. Resolving requires analyzing execution plans, index tuning, query rewriting, and continuous monitoring.

2. Practical Cases

2.1 Use EXPLAIN to inspect execution plans

Example query on an e‑commerce order‑history table:

SELECT * FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN products p ON o.product_id = p.id
WHERE o.create_time > '2025-01-01'
  AND u.vip_level > 3
  AND p.category_id IN (5, 8)
ORDER BY o.amount DESC
LIMIT 1000, 20;

EXPLAIN output shows three full‑table scans (type=ALL), a memory‑intensive filesort, and no usable index (key=NULL).

2.2 Index optimization

When the plan indicates missing indexes, create targeted indexes:

ALTER TABLE users ADD INDEX idx_vip_level (vip_level);
ALTER TABLE orders ADD INDEX idx_create_user (create_time, user_id) COMMENT 'covering composite index';

The composite index allows MySQL to satisfy the query directly from the index without touching the table.

2.3 Join optimization

Original join order: orders → users → products. Reordering to filter smaller tables first reduces row volume:

SELECT o.* FROM products p
INNER JOIN (
    SELECT id, user_id, amount, create_time
    FROM orders
    WHERE create_time > '2025-01-01'
) o ON p.id = o.product_id
INNER JOIN (
    SELECT id FROM users WHERE vip_level > 3
) u ON o.user_id = u.id
WHERE p.category_id IN (5, 8)
ORDER BY o.amount DESC
LIMIT 1000, 20;

Benefits: scans a filtered user subset (~100 rows), eliminates unnecessary column transfer, and avoids temporary tables.

2.4 Advanced optimization

If the optimizer still picks a suboptimal index, force the desired one:

SELECT /*+ INDEX(o idx_create_user) */ o.id, o.amount
FROM orders o FORCE INDEX (idx_create_user)
WHERE o.create_time > '2025-01-01';

For deep pagination, fetch primary keys first then order the small result set:

SELECT * FROM (
    SELECT id, amount
    FROM orders
    WHERE create_time > '2025-01-01'
    ORDER BY amount DESC
    LIMIT 1020   -- fetch keys for the target page
) tmp
ORDER BY tmp.amount DESC
LIMIT 20;   -- corresponds to OFFSET 1000, LIMIT 20

2.5 Monitoring

Check index usage: SHOW INDEX FROM orders; Monitor I/O statistics via Performance Schema:

SELECT object_schema, object_name, index_name, count_read, count_fetch
FROM PERFORMANCE_SCHEMA.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL;

2.6 System governance

Avoid unsafe MyBatis dynamic SQL that can inject 1=1 and trigger full scans. Vulnerable example:

@Select("SELECT * FROM orders WHERE ${condition}")
List<Order> findByCondition(@Param("condition") String condition);

Safe parameterized query:

@Select("SELECT * FROM orders WHERE create_time > #{time}")
List<Order> findByTime(@Param("time") Date time);

2.7 Massive table optimization

For tables with billions of rows, partition or shard the data. Example Sharding‑JDBC configuration partitions orders by year and user_id:

// Sharding-JDBC configuration
spring.shardingsphere.rules.sharding.tables.orders.actual-data-nodes=ds0..1.orders_${2024..2025}
spring.shardingsphere.rules.sharding.tables.orders.table-strategy.standard.sharding-column=create_time
spring.shardingsphere.rules.sharding.tables.orders.table-strategy.standard.sharding-algorithm-name=time_range

Strategies include yearly tables, user_id hash sharding, and separating hot from cold data into object storage.

2.8 Summary

Three‑step optimization process:

Diagnose: use slow‑query logs and EXPLAIN to locate problems.

Fix: simplify and decompose complex queries, apply appropriate indexes and join ordering.

Rebuild: redesign schema and partitioning to match business patterns.

Key takeaways: covering indexes outperform simple indexes, break complex joins, and ORDER BY … LIMIT does not guarantee efficient pagination.

Environment: MySQL 8.0.32