Why Most MySQL Optimization Tips Miss the Mark—and a Better Checklist

1. Misplaced Effort

Effective optimization starts by identifying the real bottleneck; about 60% of improvements come from a solid understanding of SQL fundamentals such as joins, sub‑queries, indexes, and normalization. Another 35% depends on knowing how different storage engines affect operations like COUNT(*). The remaining 5% involves obscure settings that most users never encounter.

2. Good Question, Bad Solution

MySQL stores variable‑length columns (e.g., TEXT or BLOB) using a dynamic row format, which forces sorting to disk. Instead of avoiding these types, the article recommends moving them to a separate table.

CREATE TABLE posts (
  id int UNSIGNED NOT NULL AUTO_INCREMENT,
  author_id int UNSIGNED NOT NULL,
  created timestamp NOT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE posts_data (
  post_id int UNSIGNED NOT NULL,
  body text,
  PRIMARY KEY(post_id)
);

3. Surprising Advice

Removing “unnecessary” parentheses in a WHERE clause (e.g., SELECT * FROM posts WHERE (author_id = 5 AND published = 1)) provides no measurable benefit; modern DBMSs already handle such syntax efficiently, similar to the negligible difference between i++ and ++i in C.

My Improved MySQL Optimization Checklist

1. Benchmark, Benchmark, Benchmark

Use tools like sysbench, ab, or supersmack to generate load and expose configuration problems.

2. Performance Testing

Enable the slow‑query log, install mtop, and examine queries that run longer than a few seconds. Combine MySQL utilities ( EXPLAIN, SHOW STATUS, SHOW PROCESSLIST) with system monitors ( top, vmstat) to locate resource hotspots.

3. Reduce Your Schema

Design tables with realistic size estimates; avoid oversized integer types (e.g., BIGINT) unless truly needed, and use appropriate column lengths for fixed‑format data such as ZIP codes.

4. Split Your Tables

Separate frequently updated columns from rarely accessed ones, and consider vertical or horizontal partitioning to keep hot data in memory.

CREATE TABLE posts (
  id int UNSIGNED NOT NULL AUTO_INCREMENT,
  author_id int UNSIGNED NOT NULL,
  title varchar(128),
  created timestamp NOT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE posts_data (
  post_id int UNSIGNED NOT NULL,
  teaser text,
  body text,
  PRIMARY KEY(post_id)
);

5. Avoid Overusing Artificial Primary Keys

Artificial keys are useful for stability, but they can waste space and impose limits on many‑to‑many relationships. When a natural key makes sense, prefer it.

CREATE TABLE post_tag (
  relation_id int UNSIGNED NOT NULL AUTO_INCREMENT,
  post_id int UNSIGNED NOT NULL,
  tag_id int UNSIGNED NOT NULL,
  PRIMARY KEY(relation_id),
  UNIQUE INDEX (post_id, tag_id)
);

6. Learn Indexes

Indexes (typically B‑tree) dramatically speed up lookups such as SELECT * FROM users WHERE last_name = 'Goldstein'. Index columns used in SELECT, JOIN, GROUP BY, and ORDER BY, but balance index count against storage and write overhead.

7. SQL Is Not C

SQL is a declarative language; treat it as a set‑based operation rather than procedural code. Replace correlated sub‑queries with joins for better performance.

SELECT a.id, MAX(p.created) AS latest_post
FROM authors a
INNER JOIN posts p ON a.id = p.author_id
GROUP BY a.id;

8. Understand Your Storage Engine

MyISAM excels at read‑heavy workloads with cached row counts, while InnoDB handles write‑heavy scenarios and provides row‑level locking. Be aware of how each engine processes COUNT(*) and choose accordingly.

9. MySQL‑Specific Shortcuts

Leverage extensions like INSERT … SELECT, INSERT … ON DUPLICATE KEY UPDATE, and REPLACE. Avoid risky statements such as INSERT DELAYED in high‑throughput environments, and consider index hints when the optimizer chooses poorly.