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

1. Table Design

1.1 Storage Engine Selection

MySQL uses a pluggable storage‑engine architecture. The most common engines are:

InnoDB – row‑level locking, supports transactions and (since MySQL 5.6) full‑text indexes. Requires about 2.5× the storage of MyISAM but provides higher concurrency.

MyISAM – table‑level locking, fast reads, small footprint, supports full‑text indexes up to MySQL 5.5, no transactions.

Memory – table‑level locking, data stored in RAM for maximum speed; data is lost on server restart and memory usage grows with row count.

For most applications InnoDB is the default choice; MyISAM or Memory may be used only for special cases such as legacy full‑text search or transient caching.

1.2 Partitioning Strategies

Two main partitioning methods help reduce I/O and contention:

Vertical partitioning – split a wide table into multiple tables that contain subsets of columns. This reduces resource contention, lowers lock probability, and enables tiered storage (e.g., core vs. non‑core data).

Horizontal partitioning – split a large table into many tables with identical schema based on a rule (commonly time). It mitigates performance degradation caused by very large tables and eases space management on a single server.

1.3 Index Design

MySQL provides four index types: PRIMARY, UNIQUE, INDEX, and FULLTEXT. Proper index design accelerates lookups but adds storage overhead and slows write operations. Common pitfalls:

Missing indexes on columns used in WHERE, JOIN, or ORDER BY.

Over‑indexing – creating many indexes that increase write latency.

Ignoring composite (multi‑column) indexes or placing low‑selectivity columns first.

Indexing columns with very low cardinality (e.g., status flags) which yields little benefit.

Best practices:

Use composite indexes with the most selective column first.

Include columns used for ORDER BY / GROUP BY in the index to avoid extra sorting.

Prefer prefix indexes for very long VARCHAR columns to reduce index size.

Avoid functions, arithmetic operations, or leading wildcards (%pattern) on indexed columns.

Specify explicit column lists in SELECT, INSERT, and UPDATE statements instead of *.

2. Slow‑SQL Issues

2.1 Typical Causes

Slow queries may stem from:

Poor SQL formulation (missing indexes, inefficient joins, unnecessary calculations).

Lock contention (especially with MyISAM’s table‑level locks).

Resource contention on CPU or I/O caused by concurrent workloads.

Inadequate hardware (disk latency, insufficient memory).

MySQL bugs in specific versions.

2.2 SQL‑Level Optimizations

Apply the following rules when writing queries:

Avoid type mismatches that prevent index usage (e.g., quoting numeric literals).

Do not wrap indexed columns in functions or apply arithmetic operations.

Use prefix indexes for long string columns.

Never start a LIKE pattern with a leading % if the column is indexed.

When using a composite index, query the leftmost columns first; accessing only the second or later column bypasses the index.

Prefer ASC order for sorting when possible.

Replace OR conditions with UNION for InnoDB tables.

Keep high‑selectivity columns at the beginning of a composite index.

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

Limit transaction size; large transactions increase lock time and replication lag.

Use LIMIT with proper pagination techniques (e.g., keyset pagination) instead of large offsets.

Use TRUNCATE to delete all rows instead of DELETE for better performance.

Specify column lists in INSERT / UPDATE to avoid reliance on table order.

For InnoDB, SELECT COUNT(*) is efficient because InnoDB maintains row‑count statistics.

Avoid ORDER BY RAND() on large tables.

3. Diagnostic Tools

Before deployment, analyze queries with built‑in MySQL utilities: mysqldumpslow – aggregates slow‑query log entries. mysql profile – provides per‑statement profiling information. EXPLAIN (or EXPLAIN ANALYZE in newer versions) – shows the execution plan and index usage.

Combine these tools with the slow_query_log configuration to capture problematic statements for further tuning.

4. Handling Accidental Data Loss or Bugs

When a deletion or corruption occurs:

Immediately notify the DBA and, if necessary, stop the affected service to prevent further damage.

Restore the affected tables from the most recent backup (binary log, logical dump, or snapshot).

Validate the restored data before bringing the service back online.

For critical operations, test the fix in a staging environment and perform a controlled rollout.

Preventive measures include regular backups, point‑in‑time recovery using binary logs, and automated testing of migration scripts.