Skip to content
Tutorials Center
My learning

Topics

Lesson 73 of 75 ยท Practice and Review

Common MySQL Pitfalls and Anti-Patterns

Avoid the MySQL mistakes that cost teams the most: implicit conversions, utf8 vs utf8mb4, silent truncation, OFFSET paging, N+1 queries and long transactions.

Most MySQL trouble in production does not come from exotic features. It comes from a short list of habits that work fine on a laptop with a hundred rows and fall over with a hundred million. This lesson collects the ones that show up again and again: queries that quietly skip an index, text that loses characters, pages that get slower the deeper you go, and transactions that hold locks for far too long.

Each pitfall below has the same shape: what goes wrong, why MySQL lets it happen, and the better habit. A MySQL server is not available in the browser, so server behavior is shown in read-only blocks. Where a safe, portable demonstration exists, a live SQLite example runs next to it. Every lesson here points back to the chapter that teaches the full topic, so use this page as a review checklist.

Nine habits to unlearn

Implicit casts A type mismatch can disable an index.
utf8 is not UTF-8 Use utf8mb4 everywhere.
Silent truncation Keep strict mode on.
Deep OFFSET Skipped rows are still read.

Part one, the data and query habits.

1 / 2

Implicit conversions

When you compare values of different types, MySQL converts one side so the comparison can happen. That is convenient, and it hides two separate problems.

The first is wrong matches. A string that starts with digits converts to those digits and ignores the rest:

SQL
SELECT 'abc' = 0;        -- 1, because 'abc' becomes 0
SELECT '12abc' = 12;     -- 1, because '12abc' becomes 12 (with a warning)
SELECT '1e3' = 1000;     -- 1, because '1e3' is a number in scientific notation

The second is lost indexes. Suppose phone is a VARCHAR(20) column with an index, and you write the number without quotes:

SQL
-- Index on phone is NOT used: every row's phone is converted to a number
SELECT * FROM customers WHERE phone = 5551234567;

-- Index is used: both sides are strings
SELECT * FROM customers WHERE phone = '5551234567';

In the first query MySQL cannot look the value up in the index, because the index is ordered as strings, not numbers. It has to convert every row's phone and compare, which is a full table scan. The query still returns the right rows, so nothing tells you something is wrong until the table grows. Worse, '555-1234' and '555' both convert to 555, so number-style comparisons can match rows you never meant to match.

SQLite does not convert '12abc' for you in a comparison, but it does have the same string-to-number cast function, so you can see exactly what MySQL does under the hood. Compare the two behaviors:

SQL What a string-to-number cast keeps

Press Run (or Ctrl + Enter) to see the result here.

The first three columns show what MySQL's implicit conversion would silently produce: 12, 0 and 1000.0. The last column is 0 in SQLite because SQLite refuses to compare a text value with a number. MySQL would say 1. The fix is a habit: make both sides of a comparison the same type, and bind parameters with the right type in your driver. The NULL and comparison lesson covers the rules in detail.

utf8 is not UTF-8

In MySQL, the character set called utf8 is really utf8mb3: at most three bytes per character. Real UTF-8 needs four bytes for characters outside the Basic Multilingual Plane, which includes every emoji and many rarer Chinese, Japanese and mathematical characters. Storing one in a utf8mb3 column fails in strict mode and loses data without it:

SQL
CREATE TABLE notes (body VARCHAR(100) CHARACTER SET utf8mb3);
INSERT INTO notes VALUES ('Great launch ๐ŸŽ‰');
-- ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x8E\x89' for column 'body'

The fix is utf8mb4, which is the default character set since MySQL 8.0. Problems usually come from old schemas, from a connection that still says utf8, or from a hard-coded CHARSET=utf8 in an old migration. Check all three places: the column, the table default, and the connection (charset=utf8mb4 in your DSN). The character sets lesson explains collations and how to convert an old table.

Silent truncation and invented values

Without strict mode, MySQL cuts values that are too long, clamps numbers to the column's range and turns bad dates into zeros, all with a warning that most applications never read. The remedy is to leave STRICT_TRANS_TABLES on (the default), which the SQL modes lesson covers.

You can also add constraints that state your own rules. This live example makes an out-of-range value fail instead of being stored, which is what a strict TINYINT UNSIGNED does automatically:

SQL Reject bad values instead of storing them

Press Run (or Ctrl + Enter) to see the result here.

The third insert fails with CHECK constraint failed and nothing is stored. MySQL has enforced CHECK constraints since 8.0.16; on older versions the clause was parsed and ignored, another quiet failure worth knowing about.

OFFSET pagination

LIMIT 20 OFFSET 100000 does not jump to row 100,000. MySQL reads and throws away the first 100,000 rows, then returns 20. Page 1 is fast and page 5,000 is slow, and crawlers and API clients love to walk to the last page:

SQL
SELECT id, title FROM articles
ORDER BY id
LIMIT 20 OFFSET 100000;   -- reads 100,020 rows to return 20

Keyset pagination (also called the seek method) remembers where the last page ended and asks the index to continue from there:

SQL
SELECT id, title FROM articles
WHERE id > 100000        -- the last id the client saw
ORDER BY id
LIMIT 20;

The cost is the same for every page, because the index finds the starting point directly. The trade-off is that you cannot jump to "page 37", only to the next and previous pages, which suits infinite scroll and APIs. When you sort by something non-unique, such as a date, add the id as a tie breaker: WHERE (created_at, id) > (?, ?) ORDER BY created_at, id. Here is the keyset query running live, together with the plan showing that the index does the seeking:

SQL Keyset pagination uses the index

Press Run (or Ctrl + Enter) to see the result here.

You get ids 101 to 105 and a plan that searches the primary key with id>?, with no scan from the start. The optimization lesson goes further.

Too many indexes

Indexes make reads faster and writes slower. Every INSERT adds an entry to each index, every UPDATE of an indexed column moves entries, and each index takes disk space and buffer pool memory. A table with fifteen indexes, added "just in case", does fifteen times the write work and often has several that overlap:

SQL
KEY idx_a (customer_id),
KEY idx_b (customer_id, status),     -- makes idx_a redundant
KEY idx_c (customer_id, status, created_at)

A query that filters on customer_id can use idx_c, so idx_a and idx_b do nothing except cost writes. The habit is to add indexes for real queries you have seen in EXPLAIN, review the sys.schema_redundant_indexes and sys.schema_unused_indexes views periodically (see the information_schema lesson), and test removing an index with ALTER TABLE ... ALTER INDEX idx_a INVISIBLE before dropping it for good.

SELECT * everywhere

SELECT * sends every column over the network, defeats covering indexes, and breaks when someone adds or reorders a column. If a table has a TEXT or JSON column, a list page that only needed an id and a title now downloads kilobytes per row. The live example shows the covering-index difference: naming the columns lets the database answer from the index alone.

SQL Naming columns enables a covering index

Press Run (or Ctrl + Enter) to see the result here.

The first plan says USING COVERING INDEX, meaning the table itself is never read. The second must visit the table for notes. MySQL shows the same idea as Using index in the Extra column of EXPLAIN.

N+1 queries

The N+1 problem is one query to fetch a list, then one more query per row to fetch something related. It usually hides inside an ORM loop:

PHP
$posts = $pdo->query('SELECT id, title FROM posts LIMIT 50')->fetchAll();
foreach ($posts as $post) {
    // 50 extra queries, one per post
    $stmt = $pdo->prepare('SELECT name FROM authors WHERE id = ?');
    $stmt->execute([$post['author_id']]);
}

Each query is fast, so nothing looks wrong in a slow query log, but 51 round trips add network latency and server overhead. Fetch the related rows together, with a join or with one IN query:

SQL One join instead of many lookups

Press Run (or Ctrl + Enter) to see the result here.

Three posts with their authors arrive in a single round trip. ORMs call this eager loading (with() in Laravel, select_related in Django); turn on your framework's query log in development so you notice repeated queries.

Long transactions

A transaction that stays open holds its row locks until it ends, and InnoDB must keep old row versions (undo records) alive for it. A transaction left open while the application calls a slow HTTP API, waits for a user, or loops over a large export causes:

  • Lock waits and timeouts for other sessions that need the same rows (error 1205).
  • Growing undo history, which slows reads of recently changed rows and bloats the system tablespace.
  • Replication lag, because a huge transaction reaches replicas as one block.
SQL
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- ... call a payment provider over HTTP here (seconds) ...
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

Keep transactions short: do the slow work first, then open the transaction, change what you need, and commit. For big maintenance jobs, process in batches (for example 1,000 rows per transaction). Also check for the opposite trap: with autocommit on and no explicit transaction, each statement is its own transaction, which is safe but slow for bulk loading. The transactions lesson gives the full picture.

Wrong assumptions about isolation

InnoDB's default isolation level is REPEATABLE READ, which gives a consistent snapshot to plain SELECTs. People often misread that as "my read-then-write logic is safe". It is not. This is the lost update:

SQL
-- Session A                             -- Session B
START TRANSACTION;                       START TRANSACTION;
SELECT stock FROM items WHERE id = 1;    SELECT stock FROM items WHERE id = 1;
-- sees 10                               -- sees 10
UPDATE items SET stock = 9 WHERE id = 1; UPDATE items SET stock = 9 WHERE id = 1;
COMMIT;                                  COMMIT;
-- two sales, stock went down by one

Both sessions read 10, computed 9 in the application and wrote 9. The application, not MySQL, did the arithmetic on a stale value. Two safe ways: let the database do the arithmetic in one statement, or lock the row you read with SELECT ... FOR UPDATE. The live demo plays both sessions back to back:

SQL Stale write versus atomic update

Press Run (or Ctrl + Enter) to see the result here.

Item 1 ends at 9 even though two units were sold. Item 2 correctly ends at 8, and the AND stock > 0 guard stops it from going negative. Other beliefs to drop: that REPEATABLE READ blocks all phantoms for locking reads (it does not unless you lock), and that a transaction automatically retries after a deadlock (your code must). See the isolation lesson and the locking lesson.

A quick audit: grep your code for SELECT *, for loops that run queries, for unquoted numbers compared with string columns, for CHARSET=utf8, and for OFFSET with large values. Those five searches catch most of this page.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why can WHERE phone = 5551234567 be slow when phone is an indexed VARCHAR column?

    The comparison forces a type conversion on the column side, which makes the index unusable. Quote the value so both sides are strings.
  2. 2. What is true about MySQL's character set named utf8?

    utf8 is an alias for utf8mb3. Use utf8mb4 for real UTF-8, including emoji.
  3. 3. Why does LIMIT 20 OFFSET 100000 get slower for deeper pages?

    OFFSET skips rows by reading them. Keyset pagination (WHERE id > last_seen_id) starts at the right place in the index instead.
  4. 4. Two sessions read stock 10, then each runs UPDATE items SET stock = 9. Which fix is safest?

    Computing the new value inside the statement uses the current row value. The read-then-write version let the application overwrite with a stale number.
  5. 5. What is a cost of keeping a transaction open while calling a slow web service?

    Row locks stay until commit or rollback, and InnoDB must keep old row versions for the open snapshot.

Summary

  • Compare like with like: a type mismatch converts values silently and can switch off an index.
  • Use utf8mb4 for the column, the table and the connection; utf8 means three bytes only.
  • Keep strict mode on and add CHECK constraints for your own rules so bad data fails loudly.
  • Replace deep OFFSET pages with keyset pagination, and name the columns you need instead of SELECT *.
  • Add indexes for queries you have measured, and remove redundant ones.
  • Avoid N+1 query loops with joins or batched IN queries, keep transactions short, and do read-modify-write arithmetic inside one statement or under a lock.

Next: MySQL Interview Questions.