Tables, Types & Schema for Production

Come on in, the water's fine.

This is the first post in Learn MySQL. It is a series of interactive articles for web folks who want to understand MySQL and InnoDB. Not just run php artisan migrate and hope production is in a good mood.

I'll cover foundations first: schema, indexes, queries, writes, and transactions. Then the deep stuff: MVCC, locks, the buffer pool, replication, and forensics. Read in order or skip around. Your call.

Today is tables and column types. These boring choices decide whether your app stores correct data. They also decide whether your list endpoints still feel snappy six months from now.

I know the spicy lock diagrams are waiting. But everything later in the series assumes a row already has a shape. Indexes hang off columns. Transactions flip typed values. The buffer pool caches pages full of those rows. Get a type wrong (DOUBLE for money, TIMESTAMP for "the user meant Tuesday in Arizona") and no amount of clever caching will save you. Fixing a type in a migration review takes five minutes. Fixing it two years later takes a weekend, a maintenance window, and an apology. The migration file is also where the ORM sounds most confident while it quietly makes decisions you never reviewed.

The app examples in this series are Laravel and Eloquent, because that's what I ship at my day job. Don't bail if you're on something else. Every footgun here is really a MySQL footgun wearing a PHP accent. The fix is always in the SQL.

Even if this is the only post you read, I want you to walk away with a radar for the footguns hiding in your ORM migrations. If a section never answers "why should I care?", I failed. Yell at future-me in the comments. (There are no comments. Yell into the void. Same energy.)

What a table actually is (and why that isn't pedantry)

When you CREATE TABLE in modern MySQL, you're almost certainly getting InnoDB.[1] InnoDB does not store your table as a loose bag of rows in whatever order they showed up, like a CSV on a USB stick. It builds a clustered index. It sorts the whole table by the primary key and packs it into fixed-size pages on disk. Looking up a row by primary key is not "find a pointer, then hop somewhere else." The bottom level of that index is the row. (The next post unpacks the primary-key side of this. For now, just remember that your columns live inside those pages.)

Here's the itch I want in your brain today: the types you put on columns decide what sits in those pages. The arithmetic is blunt. An InnoDB page is 16KB by default.[19] Keep rows to a tidy ~160 bytes and about a hundred of them fit in every page MySQL reads. Let them sprawl to 1.6KB and about ten fit. Same query, same answer, ten times the pages touched and ten times the cache eaten.

A migration is not "just SQL." It reshapes the physical thing every request reads. That is why I'm spending a whole article on INT vs BIGINT and whether your bio column can survive a 😀 emoji.

Types matter

Every type you pick is a vote on that page budget. There's even a hard ceiling: all the columns in a row share roughly ~65KB of space (long values often spill off-page, which is its own adventure).[18] VARCHAR(8000) on every field "just in case" is how you donate performance to the void.

Width is not the only vote. Float money, timezone-shifting event times, and utf8mb3 text are correctness bugs. CI will cheerfully green-check them, which is rude.

For each type family below: what it is, what breaks if you shrug, and what I'd pick on a normal web app.

Choosing IDs

MySQL integers come in fixed widths, from TINYINT (1 byte) up through BIGINT (8 bytes). Signed vs unsigned only changes the range, not how much space they take.[2] That's useful background. But the real decision is what you put on the primary key (and every foreign key that copies it). That value shows up in joins, indexes, exports, and often right in your URLs.

The classic default is an autoincrementing INT. It's skinny, fast, and everyone gets it. Perfect for internal tables that never leave the database. There are two catches. Signed INT tops out around 2.1 billion. And sequential ids in public URLs are an invitation: anyone who sees /orders/42 will try /orders/41 just to see what happens.

Stepping up to BIGINT mostly solves the "will I run out?" anxiety. It also stays cheap to join on. Laravel already voted here: $table->id() has meant BIGINT UNSIGNED AUTO_INCREMENT for years, and it's one of the framework defaults I'd actually defend in court. The new footgun is the frontend. Ship a huge integer as a bare JSON number and JavaScript can quietly round it. Then the id in the UI is no longer the id in MySQL.[3] "Big integer in the database" and "safe number in React" are different problems.

Sometimes you don't want a sequence at all. UUIDs and ULIDs let clients or other services mint ids without asking MySQL first. They also make nicer opaque public identifiers. The cost is width. Those keys get copied into every secondary index. And random UUIDs as the clustered primary key scatter inserts across the table (more on that in the primary-key post). If this is your PK, prefer something time-sortable like a ULID or uuid_v7.

What I reach for on a normal web app with a public API is both. Use a skinny BIGINT UNSIGNED AUTO_INCREMENT (or plain INT if I'm sure) as the clustered key for joins. Plus a unique CHAR(26) ULID-style public_id for URLs and JSON. The database keeps the cheap integer. The frontend only ever sees a string, so the Number-precision trap never shows up. In Laravel that's $table->id() next to $table->ulid('public_id')->unique(), with the HasUlids trait pointed at public_id (override uniqueIds()) so the model mints it on create. Two columns to generate and index, sure. Almost always worth it. That's what the marketplace schema below uses.

If the table is truly internal, a lone INT or BIGINT is fine. Just size it for growth. If clients need to create the row id themselves, lean on ULID/UUID. Ideally use that as the public id column, or as a time-sortable PK if you really only want one. Day-one goal either way: boring joins in the database, stable strings at the API boundary, and zero Slack threads titled "why did this id change by one??"

Poke the strategies yourself. Watch the public URL and how a fatter clustered key packs fewer rows onto a 16KB page:

Money: floats are for physics homework, not invoices

JavaScript's Number (and MySQL's FLOAT / DOUBLE) live in approximate binary floating-point land.[4][5] That's fine for "model confidence score" and "how spicy is this recommendation." It is a deeply unserious place to keep money. Store currency in a float and you are volunteering for a support ticket that says "the total is off by a cent." Plus a finance person who does not care that IEEE 754 is hard.

I like two adult options:

1. Integer minor units: price_cents as an integer, plus CHAR(3) for the currency code. This is the Stripe-shaped universe.[6]
2. DECIMAL(M,D): exact fixed-point if you'd rather keep a decimal scale in the column.[7]

In migration-builder terms that's $table->unsignedInteger('price_cents') with an 'integer' cast on the model, or $table->decimal('price', 10, 2) with a 'decimal:2' cast. The thing to never write is $table->float('price'). Laravel will create that without so much as a raised eyebrow. And watch the cast side too: put a 'float' cast on a perfectly exact DECIMAL column and you've reintroduced the bug in PHP after MySQL did everything right.

Money bugs rarely announce themselves as money bugs. They show up as "the API is wrong," "the frontend rounded weird," or "accounting export is haunted." Half the time the ghost is the column type.

Ring up a cart of ten-cent line items. Watch DOUBLE drift away from the true total while INT cents and DECIMAL stay exact:

Choosing time types

MySQL mostly gives you two ways to store "a moment," and they lie differently.[8] TIMESTAMP converts on the way in and out using the session time_zone. Change the connection timezone (or move regions and forget to pin it) and the same stored instant can display as a different local time. DATETIME just keeps the digits you gave it, no conversion. That's great for wall-clock meaning if you also know which timezone that clock lives in. And terrible if you assumed "those digits are UTC" when they weren't.

I learned this the hard way at BetterRX, the hospice pharmacy platform I work on. Patients sometimes go on respite care. That means they are temporarily housed somewhere that isn't home, so they need a different pharmacy and delivery location for a while. Users schedule that location change ahead of time. When the stay starts, the system should order meds from the new pharmacy to the new place so everything's ready on arrival. The hair-pulling part wasn't the order itself. It was the job that asks "which respite stays start now?" "Now" only makes sense in the patient's and pharmacy's timezones. Local midnight in Arizona is not local midnight in New York. Get the stored type and timezone story wrong and you fire early, late, or on the wrong calendar day. That is an especially bad genre of bug when medications are involved.

What finally stuck: treat event / schedule time ("stay starts on this local date") as a wall-clock problem. It needs an explicit timezone, or a UTC instant you convert with a library you trust. Treat row mutation time (created_at / updated_at) as "when did the database notice this row change?" That is usually fine as UTC with something like CURRENT_TIMESTAMP(3).

One more reason I lean away from TIMESTAMP for schedules: it still hits a wall in 2038.[8] Far-future dates are routine in healthcare, so I prefer DATETIME (or a deliberate UTC approach) when the calendar can look past that cliff. And don't trust the ORM to make the choice for you. Laravel's $table->timestamps() quietly creates nullable TIMESTAMP columns. That is fine for created_at bookkeeping and exactly wrong for schedules. When you mean an event time, say $table->dateTime('starts_at') and mean it. Time bugs masquerade as flaky jobs. Underneath, it's usually a column type lying about timezones.

One instant, three wall clocks. TIMESTAMP re-renders as the session timezone cycles; DATETIME is a polaroid that never changes:

Choosing string types

Two knobs decide how text behaves in MySQL: the character set (which symbols exist and how they're encoded as bytes) and the collation (the rules for comparing and sorting those symbols).[9] Get either wrong and you don't just get "weird characters." You get wrong equality, wrong ORDER BY, and rows that refuse to store the emoji your users pasted into a name field.

A character set is literally "here are the symbols, and here's the byte pattern for each one." latin1 is an old single-byte Western European set. Fine for a 1998 guestbook. Hostile to most of the modern internet. utf8mb4 is real UTF-8: every Unicode character, up to four bytes per character.[10] The trap is historical naming. For years MySQL's charset called utf8 was actually utf8mb3, a deprecated 3-byte subset that only covers the Basic Multilingual Plane.[11][12] Emoji and other supplementary-plane characters simply don't fit. That is why MySQL now tells you to use utf8mb4 for new apps. Users will paste a 😀 into a display name. Your schema should not respond with "lol truncate."

Lengths are where the byte math gets sneaky. VARCHAR(n) is n characters, but storage and row-size budgets think in bytes.[13][14] Under utf8mb4, one character can cost up to 4 bytes, so a full VARCHAR(255) is not "about 255 bytes." Worst case it's 255 × 4 = 1020 bytes of payload. MySQL also stores a length prefix in front of the value: 1 byte if the column's maximum can never pass 255 bytes, 2 bytes otherwise. A utf8mb4 VARCHAR(255) can always pass it (1020 > 255), so it pays the 2 bytes.[14] That puts the declared budget around 1022 bytes, about 1KB, for a single "probably fine" column. Actual rows only pay for the bytes you use. But indexes, row-size limits, and "how many of these fit on a page" care about that worst-case declaration. Stack a dozen VARCHAR(255) "just in case" fields on a hot list query and you've donated a lot of cache to empty aspiration.

This exact math is why a generation of Laravel developers has Schema::defaultStringLength(191) memorized without knowing what it does. When the framework switched its default charset to utf8mb4, indexes on VARCHAR(255) columns blew past an old 767-byte index-key limit. Migrations started failing with 1071: Specified key was too long. The fix everyone pasted from Stack Overflow works because 191 × 4 = 764 bytes just barely squeaks under. The bytes were always there. The emoji just sent the bill.

What I pick for normal app text: utf8mb4 with utf8mb4_0900_ai_ci (or whatever your MySQL 8+/9.x default already is). The name is less mystical than it looks. Collation suffixes are a little cheat sheet.[15] _ci / _cs mean case-insensitive vs case-sensitive. _ai / _as mean accent-insensitive vs accent-sensitive. 0900 means the collation is based on Unicode Collation Algorithm 9.0.0. So utf8mb4_0900_ai_ci says: compare as Unicode text, ignore case, ignore accents. That is usually what you want for names, emails, and search-ish equality. Reach for _bin when you need raw byte identity (tokens, case-sensitive codes). Reach for a language-specific collation (utf8mb4_tr_0900_ai_ci, etc.) when a locale's sort rules are a product requirement, not before.

What I avoid: latin1 and friends inherited from stone-tablet tutorials. Also anything still declaring CHARSET utf8 / utf8mb3 for user-facing text. And cargo-cult VARCHAR(255) on every column. That 255 usually arrives via $table->string('title') with no second argument. Pass the real one: $table->string('title', 140), where 140 came from the product ("display name max 80 in the UI"), not from "255 feels traditional." Fixed-ish identifiers (CHAR(26) ULIDs, CHAR(3) currency codes) can stay CHAR/VARCHAR at the real width. Long blobs of prose belong in TEXT (or JSON, if the shape is sparse), not a comically wide VARCHAR pretending it's a memo field.

Charset mistakes show up as weird characters (or hard errors) in prod. Length mistakes show up later as mysterious row bloat. Collation mistakes show up as "why didn't this unique email match?" All of them are cheaper to fix in the migration PR than in a war room.

Type some text below. Each character becomes a tile as wide as its byte cost. Emoji fatten under utf8mb4 and shatter under utf8mb3:

NULL is a third state (not "empty," not "zero," not "the frontend forgot")

SQL NULL means unknown / not applicable.[16] It is not ''. It is not 0. Comparisons against it return neither true nor false but unknown, and WHERE only keeps rows that are actually true. So WHERE discount_code != 'BLACKFRIDAY' silently drops every row where discount_code is NULL. That is technically correct three-valued logic. And practically a bug report.[16]

Rows ride a conveyor into payment_type != 'card'. TRUE passes, FALSE drops left, and NULL falls through a trapdoor, filtered out of both branches:

Unique indexes have their own NULL surprise: MySQL allows multiple NULLs in a unique nullable column.[20] $table->string('external_id')->nullable()->unique() compiles fine, migrates fine, and then lets a thousand rows share "no external id" while everyone assumes it means one. Sometimes that's exactly what you want. Decide, don't discover.

If the domain always has a value, say NOT NULL and give a real default. If "missing" is a product concept, keep NULL on purpose and teach your API the same story. Half of "why didn't this row match my filter?" bugs are NULL semantics wearing a trench coat.

JSON: a deliberate escape hatch, not a junk drawer

Native JSON is validated binary storage, better than stuffing a blob into TEXT and praying.[17] I like it for sparse per-tenant attributes, feature-flag bags, and "seller added three weird facets" shape that shouldn't force twenty nullable columns.

I do not like it for money, foreign keys, or the fields you filter list endpoints on. If it's a join key or a WHERE you care about, it wants to be a column (I'll get into generated columns / indexing JSON later in the series). JSON feels flexible until your "flexible" field is the reason you can't index the query that pays the bills.

Why the row's byte budget matters

Remember the "rows live in pages" bit? Now stack the choices.

Every column type is a little vote for how wide a row is. Fatter rows mean fewer rows per InnoDB page. That means more I/O and cache pressure for the same "show me a page of results" API. Flip the presets and watch rows-per-page (and pages touched for a LIMIT 50) jump:

A marketplace schema that holds up

Healthcare schemas are a paperwork festival, so the worked example is a tiny marketplace instead: users, listings, order lines. Same type decisions, less PHI.

First, the anti-pattern. Here's a migration that would sail through review, because nothing in a Laravel migration file looks dangerous:

Schema::create('listings_bad', function (Blueprint $table) {
    $table->increments('id');                   // INT, like it's 2014
    $table->unsignedInteger('shop_id');
    $table->string('title');                    // VARCHAR(255), unreviewed
    $table->string('description', 8000);        // "it's basically text, right?"
    $table->float('price');                     // TODO: money stuff later
    $table->char('currency', 3)->nullable();
    $table->timestamp('starts_at')->nullable(); // event time, session-TZ type
    $table->text('attrs');                      // JSON we pinky-swear to parse
    $table->timestamps();
});

Which lands in MySQL as roughly:

-- Bad: looks fine until money, time, emoji, or row width bite
CREATE TABLE listings_bad (
  id INT AUTO_INCREMENT PRIMARY KEY,
  shop_id INT NOT NULL,
  title VARCHAR(255),
  description VARCHAR(8000),
  price DOUBLE NOT NULL,
  currency CHAR(3),
  starts_at TIMESTAMP NULL,
  attrs TEXT,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
) DEFAULT CHARSET = utf8;

Why this is a trap, in plain language: float money, utf8 quietly meaning mb3 (courtesy of a crusty 'charset' => 'utf8' in config/database.php that nobody has touched since it was scaffolded), event time on TIMESTAMP, JSON-shaped data stuffed in TEXT, nullable everything, and a description column trying to eat the whole row budget for lunch.

Now a version I'd actually defend (load Marketplace v1 in the row-budget demo above and compare the strip):

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) NOT NULL,          -- ULID-style for APIs
  email VARCHAR(320) NOT NULL,
  display_name VARCHAR(80) NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_users_public_id (public_id),
  UNIQUE KEY uk_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE listings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  shop_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(140) NOT NULL,
  description TEXT NOT NULL,
  price_cents INT UNSIGNED NOT NULL,
  currency CHAR(3) NOT NULL,
  status TINYINT UNSIGNED NOT NULL DEFAULT 1,
  starts_at DATETIME(0) NOT NULL,       -- event time, not TZ-auto
  attrs JSON NULL,                      -- sparse facets only
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL
    DEFAULT CURRENT_TIMESTAMP(3)
    ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_listings_shop_status (shop_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE order_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  listing_id BIGINT UNSIGNED NOT NULL,
  quantity SMALLINT UNSIGNED NOT NULL,
  unit_price_cents INT UNSIGNED NOT NULL,
  line_total_cents INT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  KEY idx_order_items_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

What I optimized for: exact money, event time that doesn't shapeshift with session TZ, utf8mb4, lengths tied to product reality, JSON only for sparse junk, and description as TEXT so it isn't pretending to be a polite in-row VARCHAR.

You can express all of that in the schema builder without dropping to raw SQL: $table->id(), $table->char('public_id', 26)->unique(), $table->string('title', 140), $table->text('description'), $table->unsignedInteger('price_cents'), $table->char('currency', 3), $table->dateTime('starts_at'), $table->json('attrs')->nullable(). The builder isn't the enemy. Unread defaults are.

Secondary indexes and foreign keys get their own episodes. Don't bolt them on here and call it a day. I'll earn those later.

Checklist before you migrate

Steal this for PR review. If you can't check a box, that's the comment to leave:

• IDs: width, signedness, and JSON/API round-trip decided together
• Money: integer cents or DECIMAL, never float
• Event time vs created_at/updated_at: different jobs; anything a job compares to "now" has an explicit timezone story
• User text: utf8mb4, length tied to the UI
• NULL only where "unknown" is real
• JSON only for sparse shape; filters stay relational
• Wide columns justified, or moved to TEXT / off the hot list path
• Staging and prod agree on strict SQL mode (silent truncation on your laptop is not a personality trait)
• You looked at the actual SQL (php artisan migrate --pretend, or SHOW CREATE TABLE after) and it matches the types you think your models have

Correct types prevent bugs no cache will fix. Next up: primary keys and the clustered index. Once the leaf has a shape, you still have to pick what InnoDB sorts the whole table by. That's where "I just used autoincrement" becomes a whole worldview.

References

1. [1]MySQL 9.7 Reference Manual — Introduction to InnoDB
2. [2]MySQL 9.7 Reference Manual — Integer Types (Exact Value)
3. [3]MDN — Number.MAX_SAFE_INTEGER
4. [4]MDN — Number (IEEE 754 doubles, safe integers, JSON coercion)
5. [5]MySQL 9.7 Reference Manual — Floating-Point Types (Approximate Value)
6. [6]Stripe Docs — Zero-decimal currencies and minor units
7. [7]MySQL 9.7 Reference Manual — Fixed-Point Types (Exact Value)
8. [8]MySQL 9.7 Reference Manual — Date and Time Data Types
9. [9]MySQL 9.7 Reference Manual — Character Sets and Collations in General
10. [10]MySQL 9.7 Reference Manual — The utf8mb4 Character Set
11. [11]MySQL 9.7 Reference Manual — The utf8mb3 Character Set (deprecated)
12. [12]MySQL 9.7 Reference Manual — The utf8 Character Set (deprecated alias for utf8mb3)
13. [13]MySQL 9.7 Reference Manual — The CHAR and VARCHAR Types
14. [14]MySQL 9.7 Reference Manual — Data Type Storage Requirements
15. [15]MySQL 9.7 Reference Manual — Collation Naming Conventions
16. [16]MySQL 9.7 Reference Manual — Working with NULL Values
17. [17]MySQL 9.7 Reference Manual — The JSON Data Type
18. [18]MySQL 9.7 Reference Manual — Limits on Table Column Count and Row Size
19. [19]MySQL 9.7 Reference Manual — The Physical Structure of an InnoDB Index
20. [20]MySQL 9.7 Reference Manual — Unique Indexes (CREATE INDEX Statement)