Master MySQL: Table Design, Slow Query Fixes, and Recovery Strategies

1. Table Design

1.1 Engine Selection

MySQL 5.1 introduced a plug‑in storage‑engine architecture that lets developers choose the engine that best fits the application without changing code. Common engines are MyISAM, InnoDB and Memory, each with distinct characteristics:

MyISAM : table‑level lock, fast reads, small footprint, supports full‑text index only in MySQL 5.5 and earlier, no transactions.

InnoDB : row‑level lock, stronger concurrency, uses about 2.5× the space of MyISAM, supports transactions and (since 5.6) full‑text index.

Memory : table‑level lock, data stored in RAM for very fast access, but data is lost on server restart.

For most cases the recommendation is to use InnoDB; MyISAM or Memory should be considered only for special needs such as full‑text search or extreme performance.

1.2 Partitioning Methods

Partitioning reduces server load and query time. Two main types exist:

Vertical partitioning : split a wide table into multiple tables with fewer columns, improving resource contention, lock probability, and allowing core vs. non‑core data separation.

Horizontal partitioning : split rows of a large table into multiple tables based on rules (e.g., time), addressing performance and storage issues caused by oversized tables.

1.3 Index Issues

Indexes speed up data retrieval but incur storage and maintenance costs. MySQL provides four index types: PRIMARY, UNIQUE, INDEX, and FULLTEXT (MyISAM and InnoDB ≥ 5.6). Common pitfalls include missing indexes, overusing indexes, ignoring composite indexes, and creating indexes on low‑selectivity columns.

2. Slow SQL Issues

2.1 Causes of Slow SQL

Slow queries can stem from many sources, not only poorly written SQL. Typical causes, ordered by likelihood, are:

SQL writing problems

Lock contention

IO/CPU resource competition between business instances

Server hardware limitations

MySQL bugs

2.2 Optimizing Slow SQL Caused by Query Writing

Key practices for writing efficient SQL include:

Avoid type conversions that prevent index usage (e.g., mismatched string/number quoting).

Do not wrap indexed columns in functions.

Avoid arithmetic on indexed columns.

Consider indexing only a prefix of long strings.

Leading wildcards in LIKE prevent index use.

When using composite indexes, query the leading columns first.

Never use SELECT *; specify needed columns.

Prefer ascending order for sorting.

Replace OR conditions with UNION (for InnoDB).

Place high‑selectivity columns first in composite indexes.

Include ORDER BY / GROUP BY columns in indexes to avoid extra sorting.

Additional recommendations:

Avoid large transactions that hurt concurrency and replication.

Be careful with LIMIT pagination.

Use TRUNCATE instead of DELETE to clear a table.

Minimize unnecessary MySQL work such as calculations.

Write explicit column lists to protect against schema changes.

For InnoDB, SELECT COUNT(*) can be efficient because statistics are stored.

Use ORDER BY RAND() sparingly.

3. Diagnostic Tools

Common utilities for pre‑deployment SQL analysis include:

mysqldumpslow

mysqlprofile

mysqlexplain

Detailed usage can be found in online resources.

4. Handling Mistakes and Bugs

When accidental data loss or corruption occurs, do not panic. Contact the DBA immediately, perform a data restore, and if necessary stop the service to limit impact. Mature organizations have backup and disaster‑recovery procedures; for critical data, thorough testing before release is essential.