Comprehensive Guide to MySQL Database Optimization: SQL Tuning, Index Strategies, and Best Practices

Preface

The previous article discussed MySQL index optimization; this piece expands to overall MySQL database optimization, a topic frequently asked in interviews.

SQL Optimization Steps

1. Use show status to understand execution efficiency

show [session | global] status;

You can add session (default, current connection) or global (since server start) to view specific statistics. Parameters such as Com_select , Com_insert , Com_update , and Com_delete indicate the number of executions for each statement type.

2. Identify low‑efficiency SQL statements

Use show processlist to monitor running queries in real time.

Check the slow‑query log for problematic statements.

3. Analyze execution plans with explain or desc

Refer to the previous article "MySQL Explore: Explain Execution Plan Details" for deeper insight.

4. Profile queries with show profile

show profile reveals where time is spent; show profiles provides a clearer view of the execution process.

5. Trace optimizer decisions with trace (MySQL 5.6)

The trace feature shows why the optimizer chose plan A over plan B.

6. Apply appropriate optimization measures based on the identified problems.

Common SQL Statement Optimization Methods

Avoid using != or <> in WHERE clauses, which can prevent index usage.

Build indexes on columns used in WHERE and ORDER BY .

Do not compare a column with NULL in WHERE ; instead, ensure the column has a default non‑null value.

Avoid OR in WHERE when one side lacks an index; it forces a full table scan.

Leading wildcard searches (e.g., LIKE '%c%' ) cause full scans; use a prefix wildcard ( LIKE 'c%' ) to leverage indexes.

Prefer NOT IN or BETWEEN over IN ; use EXISTS when appropriate.

Parameters in WHERE can prevent index usage because the optimizer cannot know their values at compile time.

Avoid expressions or functions on indexed columns in WHERE (e.g., num/2=100 ); rewrite as num=200 .

Update only necessary columns; updating many columns increases log volume and CPU cost.

When using composite indexes, the leftmost column must be referenced to benefit from the index.

Indexes on low‑cardinality columns (e.g., gender) may not improve performance.

Too many indexes degrade INSERT / UPDATE speed; keep the total number reasonable (≤ 6).

Avoid updating clustered index columns because it forces row reordering.

Prefer numeric fields over character fields for pure numbers to reduce comparison cost.

Never use SELECT * ; list only required columns.

For large joins, paginate before joining to reduce logical reads.

Use temporary tables judiciously; they can improve repeated‑access queries but should be truncated/dropped promptly.

Prefer SELECT INTO over CREATE TABLE for bulk inserts to reduce logging.

Avoid cursors; if necessary, use FAST_FORWARD for better performance.

Set SET NOCOUNT ON at the start of stored procedures and SET NOCOUNT OFF at the end.

Limit result set size sent to clients; avoid large data transfers.

Prefer WHERE to HAVING because HAVING filters after aggregation, incurring extra sorting.

Use TRUNCATE instead of DELETE for whole‑table deletions to avoid rollback logging.

Alias tables in multi‑table queries to reduce parsing time and avoid column ambiguity.

Replace UNION with UNION ALL when duplicate elimination is unnecessary.

Replace ORDER BY with WHERE when possible; ORDER BY uses indexes only if all ordered columns are in the same index and non‑null.

Avoid DISTINCT , UNION , MINUS , INTERSECT , and unnecessary ORDER BY because they trigger costly sorting.

Prefer PreparedStatement over Statement for parameterized queries.

Common Index Optimization Methods

Refer to the linked article "One Article to Master Index Optimization" for detailed rules.

MySQL Optimization Goals, Common Misconceptions, and Basic Principles

Optimization Goals

Reduce I/O operations, which dominate >90% of database workload.

Lower CPU consumption by minimizing expensive operations such as ORDER BY , GROUP BY , and DISTINCT .

Common Misconceptions

Assuming COUNT(1) or COUNT(primary_key) is faster than COUNT(*) . In many engines (e.g., MyISAM) COUNT(*) is optimized.

Believing COUNT(column) equals COUNT(*) ; they differ because the former skips NULLs.

Thinking fewer selected columns always reduces I/O; only when the query can be satisfied by an index does column reduction matter.

Assuming ORDER BY always requires sorting; if the index order matches the requested order, sorting is skipped.

Misinterpreting Using filesort in EXPLAIN as a guarantee of disk‑based sorting.

Basic Principles

Minimize joins; MySQL’s optimizer struggles with complex multi‑table joins.

Minimize sorting; use indexes to satisfy order requirements.

Avoid SELECT * ; list needed columns.

Prefer JOIN over subqueries for better performance.

Reduce use of OR ; consider UNION ALL or UNION instead.

Prefer UNION ALL over UNION when duplicate rows are acceptable.

Filter early in the query plan to reduce row counts before joins.

Avoid type conversions in WHERE clauses.

Prioritize optimizing high‑concurrency queries over rarely executed large queries.

Adopt a holistic view; don’t optimize a single statement at the expense of overall system performance.

Explain every frequently executed SQL to understand its execution plan.

Table Structure Optimization

Because MySQL stores rows, reducing row size increases the number of rows per page, lowering I/O and improving cache hit rates.

Data Type Selection

Prefer integer types ( TINYINT , INT , BIGINT ) with appropriate size; avoid DOUBLE and DECIMAL unless necessary.

Use BIGINT for primary keys on 64‑bit systems for better alignment.

Prefer CHAR for fixed‑length strings and VARCHAR for variable length, setting a realistic maximum length.

Prefer TIMESTAMP over DATETIME ; use DATE when only the day is needed.

Use ENUM for status fields and SET for multi‑value attributes to save space.

Avoid storing large BLOB/CLOB data in the database; use external storage instead.

Choose the smallest appropriate character set (e.g., latin1 for pure ASCII) to reduce storage.

Consider vertical partitioning for large TEXT/VARCHAR columns that are rarely accessed.

Apply moderate redundancy for frequently joined small columns, ensuring data consistency.

Prefer NOT NULL with sensible defaults; NULLs can hinder index efficiency.

Cache Parameter Optimization

Not covered in depth; generally has limited impact compared to query and schema tuning.

Conclusion

Database optimization focuses on SQL statements & indexes, table structure, system configuration, and hardware, in that order of impact.

Key steps: design a normalized schema, choose the right storage engine, tune SQL, create effective indexes, optimize table definitions, adjust DB parameters, consider sharding/partitioning, use replication and read/write splitting, apply load balancing, and finally upgrade hardware.