MySQL Database Design, Slow Query Issues, and Handling Mistakes

MySQL is a widely used relational database, especially popular for small to medium web sites due to its small footprint, multi‑processor support, open‑source nature, and free license. However, improper SQL writing and sub‑optimal design can cause performance and functional problems.

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 space, supports full‑text index (up to MySQL 5.5), no transactions.

InnoDB : row‑level lock, stronger concurrency, larger space (≈2.5× MyISAM), supports transactions, full‑text index from MySQL 5.6.

Memory : table‑level lock, data stored in RAM (fast but volatile), space proportional to data size.

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 extremely high execution speed.

1.2 Partitioning Methods

Partitioning reduces server load and query time. Two main types are vertical partitioning (splitting columns into separate tables) and horizontal partitioning (splitting rows into multiple tables with the same schema).

Vertical partitioning addresses resource contention, reduces lock probability, enables tiered storage, and eases synchronization pressure.

Horizontal partitioning (e.g., time‑based sharding) solves performance issues caused by overly large tables and mitigates single‑server storage limits.

1.3 Index Issues

Indexes accelerate 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 indexing low‑selectivity columns.

2. Slow SQL Problems

2.1 Causes of Slow Queries

Slow queries stem from many sources, not only poor SQL writing. Typical causes, ordered by likelihood, are:

SQL coding issues

Lock contention

Resource contention between business instances (IO/CPU)

Server hardware limitations

MySQL bugs

2.2 SQL‑Writing Optimizations

When the SQL itself is the bottleneck, follow these guidelines:

Avoid type conversions that bypass indexes (e.g., mismatched string/number quoting).

Do not wrap indexed columns in functions.

Do not apply arithmetic operations to indexed columns.

Consider prefix indexes for long strings.

Leading wildcards (LIKE %xxx) prevent index use.

Queries that use only the second or later column of a composite index miss the index.

Avoid SELECT *; specify needed columns.

Prefer ascending ORDER BY for better index utilization.

Replace OR with UNION when possible (InnoDB).

Place high‑selectivity columns first in composite indexes.

Include ORDER BY / GROUP BY columns in indexes to reduce sorting.

Avoid large transactions that hurt concurrency and replication.

Use LIMIT for pagination wisely.

Use TRUNCATE instead of DELETE to remove all rows.

Minimize unnecessary MySQL work such as calculations.

Write explicit column lists to protect against schema changes.

Prefer SELECT COUNT(*) on InnoDB because it stores statistics.

Use ORDER BY RAND() sparingly.

3. Diagnostic Tools

Before deployment, analyze SQL with tools such as:

mysql dumpslow

mysql profile

mysql explain

Detailed usage can be found in online resources.

4. Dealing with Mistakes or Bugs

When accidental operations or bugs cause data loss or corruption, remain calm and immediately involve a DBA. Perform data recovery or, if necessary, stop the service to limit impact. Established enterprises usually have backup and disaster‑recovery procedures; ensure critical data is thoroughly tested before release.

PS: If you find this sharing useful, feel free to like or forward it.