Mastering SQL Optimization: Index Choice, Performance Metrics, and Safe Delete Strategies

In real‑world SQL scenarios, performance tuning is crucial; a developer once spent five days wrestling with a single query before seeking expert help. The article presents practical guidance on common optimization problems.

1. Choosing the Correct Index for Mixed Conditions

Question: If a table has two indexes, idx_int_c1 on an integer column and idx_varchar_c2 on a varchar column, which index will be used for the query SELECT * FROM tb WHERE c1=100000 OR c2='zhishutang'; and why?

Answer: Most people instinctively pick the first index, but that is often wrong. Index selection should be based on cost‑based optimization rather than raw speed. When both predicates can be satisfied, the optimizer may favor the index that appears earlier, but if only one predicate matches, it chooses the index with the lowest estimated cost. The guiding principle is to let the “idle” index do the work, selecting the most suitable plan for the current data distribution and hardware.

2. Quantifying Query Performance (With vs. Without LIMIT)

Question: Between a query that returns all rows and one that adds LIMIT , both completing under one second, which is truly better and how can we prove it?

Approach: Execute both statements in a terminal and compare their execution statistics. For example, the unrestricted query might read 1 key and then 17 rows, while the limited version reads 1 key and only 4 rows. Fewer row reads indicate less I/O and better performance, providing a concrete metric to judge query efficiency.

3. Safe Handling of Long‑Running DELETE Statements

Question: A DELETE statement has been running for over two hours; can it be rolled back?

Answer: Rolling back a massive DELETE is not feasible and may worsen the situation. Instead of forcefully restarting the server, first run SHOW ENGINE INNODB STATUS\G to inspect the current transaction state and execution time. If the statement is idle, it can be safely killed; otherwise, consider alternative strategies such as batch deletions.

The article is based on material from senior database expert Wu Bingxi’s online class.