MySQL Gotchas: InnoDB vs MyISAM, Charsets, and AUTO_INCREMENT

MySQL has years of quirky decisions baked into its defaults. Most of them are fine once you know about them — but you need to know about them before they bite production.

1. utf8 is not actually UTF-8

This is the most expensive bug in MySQL history. MySQL's utf8 is a 3-byte subset of UTF-8 that can't store emoji or any character above U+FFFF.

``sql CREATE TABLE messages (text VARCHAR(255) CHARSET utf8); INSERT INTO messages VALUES ('Hello 👋'); -- ERROR or silently truncates depending on mode ``

The actual UTF-8 charset in MySQL is called utf8mb4 (the mb4 means "max 4 bytes per character"). Always use utf8mb4:

``sql CREATE TABLE messages ( text VARCHAR(255) ) CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ``

In MySQL 8.0+, the default was changed to utf8mb4, but if you're on 5.7 or earlier the default is still the broken utf8. Always specify explicitly.

2. InnoDB vs MyISAM — there's only one choice now

The only reason to use MyISAM today is reading legacy code. For anything new: InnoDB.

If you inherit MyISAM tables, migrate them:

``sql ALTER TABLE legacy_table ENGINE=InnoDB; ``

3. AUTO_INCREMENT has gaps — that's normal

AUTO_INCREMENT is not guaranteed to produce sequential, gap-free IDs. Gaps happen when:

• A transaction inserts a row, then rolls back (the ID is consumed but the row isn't there)
• A failed INSERT consumed an ID
• MySQL restarted with auto_increment_increment > 1 (replication setups)

This breaks naive assumptions like "if I see ID 100, there are 100 rows."

If you need a gap-free sequence, use a separate counter table or compute it at read time.

4. AUTO_INCREMENT reset behavior changed in 8.0

In MySQL 5.7, AUTO_INCREMENT counters were stored in memory only. On a restart, MySQL would re-read MAX(id) + 1 from the table.

That means deleting the last few rows and restarting would reuse those IDs. If your app cached old IDs (in URLs, in API responses), you could have ID collisions.

MySQL 8.0 fixed this — counters persist across restarts. But if you maintain 5.7 systems, this is the gotcha behind "wait, this user ID exists twice?"

5. VARCHAR(n) vs TEXT — size matters more than you think

VARCHAR(n) stores up to n characters (not bytes!). The space used depends on charset:

• utf8mb4: up to 4 bytes per character
• A VARCHAR(255) with utf8mb4 can use up to 1020 bytes

Index size limits: InnoDB's default max index key length is 767 bytes (3072 with innodb_large_prefix on, default in 8.0). So you can index VARCHAR(255) with utf8mb4 but barely — and VARCHAR(256) can exceed the limit.

This is the source of "indexes silently truncated" warnings in older MySQL.

6. TINYINT(1) is BOOLEAN — the display width is cosmetic

``sql CREATE TABLE flags (active TINYINT(1)); ``

The (1) is just a display hint — TINYINT(1) and TINYINT(255) store the same range (-128 to 127). The number doesn't constrain the value.

BOOLEAN and BOOL are aliases for TINYINT(1). TRUE is 1, FALSE is 0.

7. DATETIME vs TIMESTAMP behave very differently

If your app handles users across timezones and you store DATETIME, you have to remember to always convert to UTC yourself. With TIMESTAMP, MySQL handles it.

But TIMESTAMP overflows in 2038. Most modern apps use DATETIME stored as UTC by convention.

8. Empty string vs NULL in PRIMARY KEYs

``sql CREATE TABLE users (email VARCHAR(255) PRIMARY KEY); INSERT INTO users VALUES (''); -- works INSERT INTO users VALUES (''); -- ERROR: duplicate INSERT INTO users VALUES (NULL); -- ERROR: PK can't be null ``

Empty string is a valid value; NULL isn't allowed in primary keys. This trips people up when migrating data from systems that allow null.

9. LIKE is case-insensitive by default (depends on collation)

``sql SELECT * FROM users WHERE name LIKE 'alice'; -- May or may not match 'Alice' depending on collation ``

The default *_ci collations (e.g. utf8mb4_unicode_ci) are case-insensitive. Switch to *_cs collations for case-sensitive — or use LIKE BINARY:

``sql SELECT * FROM users WHERE name LIKE BINARY 'alice'; ``

10. SELECT FOR UPDATE releases the lock at commit, not statement end

``sql START TRANSACTION; SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- ... do work ... UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT; -- lock released here ``

A common bug: dev tools that auto-commit each statement (or set autocommit=1) release the FOR UPDATE lock immediately, making the lock useless. Always use explicit transactions around FOR UPDATE.

11. SHOW PROCESSLIST truncates queries

``sql SHOW PROCESSLIST; -- Info column truncated at 100 chars ``

If you're debugging a slow query and need the full text:

``sql SHOW FULL PROCESSLIST; ``

Or query information_schema.PROCESSLIST directly.

12. LIMIT N OFFSET M is O(N+M)

``sql SELECT * FROM logs ORDER BY id LIMIT 10 OFFSET 1000000; -- Reads 1,000,010 rows, returns 10 ``

For deep pagination, use cursor-based pagination instead:

``sql SELECT * FROM logs WHERE id > LAST_SEEN_ID ORDER BY id LIMIT 10; ``

This is index-friendly and O(log N).

Need help with a MySQL performance issue?

If your DB has gotten slow as your data has grown, we tune MySQL for production. Most slowness comes from a handful of fixable issues: missing indexes, schema mistakes, or query patterns that don't scale.