Master MySQL Performance: 13 Essential SQL Optimization Techniques

Introduction

Hello, I’m Tianlu. With the hiring rush of "golden three and silver four" underway, I’ve prepared a concise 13‑question series on SQL optimization that will help you tackle everyday performance challenges.

1. How do you optimize SQL in daily work?

Analyze slow‑query logs

Use EXPLAIN to view execution plans

Index optimization

Deep‑pagination optimization

Avoid full table scans

Select only required columns (e.g., SELECT id, name instead of SELECT *)

Choose appropriate data types (e.g., INT instead of VARCHAR)

Refactor SQL structure (e.g., improve JOIN usage)

Batch updates/deletes

Regularly clean unused data

Consider sharding or partitioning

Read/write splitting

2. Have you encountered deep‑pagination issues? How to solve them?

Two common approaches are the Tag Record Method and the Deferred Association Method .

Mark the last retrieved row and start the next scan from that point, similar to placing a bookmark in a book.

Example using the tag record method (assume the last processed id is 100000):

SELECT id, name, balance FROM account WHERE id > 100000 LIMIT 10;

This leverages the id index and works well when a monotonically increasing column exists.

Deferred association moves the filter to the primary‑key index, reducing table lookups:

SELECT id, name, balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.create_time > '2020-09-19' LIMIT 100000,10) acct2 ON acct1.id = acct2.id;

3. Understanding the EXPLAIN execution plan

Running EXPLAIN with a query shows how MySQL’s optimizer will process the statement, including join order and index usage.

Typical output highlights the following columns: type – join type (e.g., system, const, ref, ALL) rows – estimated rows examined filtered – percentage of rows passing the filter extra – additional details (e.g., Using filesort, Using temporary) key – index actually used

3.1 type

Join types from best to worst:

system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

3.2 rows

Estimated number of rows MySQL expects to read; for InnoDB it is a rough estimate.

3.3 filtered

Percentage of rows that satisfy the WHERE clause after filtering.

3.4 extra

Using filesort – MySQL must sort on disk

Using index – covering index is used

Using temporary – temporary table created (often for GROUP BY)

Using where – WHERE clause applied

Using index condition – index condition pushdown (MySQL 5.6+)

3.5 key

Shows the actual index chosen; compare with possible_keys for alternatives.

Tip: combine EXPLAIN with SHOW WARNINGS to see the final execution plan after optimization.

4. Optimizing large tables

Database design : split tables, use partitioning, add appropriate indexes, avoid redundant columns and excessive joins.

Index optimization : add/modify indexes, use covering or composite indexes, clean unused indexes, limit to ~5 indexes per table.

Partitioning : divide a large table into smaller partitions to improve query and update performance.

Data archiving : regularly archive historical data to reduce table size.

Cache layer : use Redis or similar to cache frequently accessed data.

SQL tuning : keep statements simple, avoid unnecessary subqueries, replace OR with more efficient constructs.

Sharding : for tens of millions of rows, consider database‑level sharding.

5. Common causes of MySQL slow queries

Typical reasons include missing indexes, full table scans, inefficient joins, large temporary tables, improper configuration, and hardware bottlenecks. (See the linked article for a detailed 12‑point list.)

6. How to use indexes to optimize SQL queries

Add suitable indexes on columns used in WHERE, GROUP BY, ORDER BY.

Choose the right index type (B‑tree for range queries, hash for equality).

Avoid indexing low‑cardinality or frequently updated columns.

Consider composite indexes respecting the left‑most prefix rule.

Use EXPLAIN to verify index usage.

Do not over‑index; excessive indexes increase storage and write overhead.

7. Slow‑SQL troubleshooting workflow

Check the slow‑query log.

Analyze the execution plan with EXPLAIN.

Enable PROFILING to see actual resource consumption.

Use Optimizer Trace for detailed optimizer decisions.

Identify the bottleneck and apply the appropriate fix.

7.1 Viewing the slow‑query log

Enable it with SET GLOBAL slow_query_log = ON; and check variables via SHOW VARIABLES LIKE 'slow_query_log%';.

7.2 Using EXPLAIN

Run EXPLAIN SELECT … to see the optimizer’s plan and verify index usage.

7.3 Profiling execution time

Turn on profiling with SET profiling = ON;, then SHOW PROFILES; and SHOW PROFILE FOR QUERY n; to view IO, CPU, and memory costs.

7.4 Optimizer Trace

Enable with SET optimizer_trace='enabled=on';, execute the query, then query SELECT * FROM information_schema.optimizer_trace; to see the join preparation, optimization, and execution phases.

7.5 Applying fixes

Most slow SQLs are index‑related – add or adjust indexes.

Rewrite inefficient queries (e.g., reduce subqueries, batch updates).

Consider using Elasticsearch or a data warehouse for analytics‑heavy workloads.

Shard large tables if necessary.

Discuss parameter tuning with a DBA.

Archive stale data.

8. Interview question: Optimizing a long‑running SQL

Approach the problem by:

Identifying the bottleneck (log analysis, EXPLAIN, profiling, optimizer trace).

Optimizing indexes.

Refactoring the SQL.

Tuning MySQL parameters.

Analyzing lock contention.

Upgrading hardware if needed.

9. Database design optimization tips

Avoid NULL columns where possible.

Choose appropriate data types and lengths.

Use indexes wisely.

Minimize TEXT columns.

Keep table structures simple and avoid excessive fields.

Apply selective redundancy.

Write efficient SQL.

10. Ten practical tips for writing high‑quality SQL

Never use SELECT *; specify columns.

Let small tables drive large tables.

Optimize LIKE patterns.

Avoid functions on indexed columns.

Batch inserts/updates when possible.

Use LIMIT to restrict result sets.

Prefer EXISTS over IN when appropriate.

Limit the number of elements in an IN list.

Prefer UNION ALL to UNION when duplicates are not a concern.

11. What is index merge?

When a query can benefit from multiple indexes, MySQL may combine them using the Index Merge optimization, allowing the engine to use the best index combination and avoid full scans.

Example:

SELECT * FROM orders WHERE customer_id = 1 AND order_date >= '2022-01-01' AND order_date < '2022-02-01';

MySQL can use both customer_id and order_date indexes, reducing the scanned rows dramatically. Verify with EXPLAIN – look for Using index merge in the extra column.

12. Optimizing slow ORDER BY queries

Sorting can be costly if MySQL must create a temporary file. Ensure the ordering column is indexed, or adjust max_length_for_sort_data and sort_buffer_size parameters.

13. Optimizing slow GROUP BY queries

Group‑by may trigger temporary tables and sorting. Mitigate by:

Adding indexes on the grouped columns.

Using ORDER BY NULL to suppress sorting.

Keeping temporary tables in memory (increase tmp_table_size if needed).

Applying SQL_BIG_RESULT for large result sets.