Context
Zipy stored session data in MySQL on InnoDB. Sessions were identified by UUIDs, and a large share of queries looked sessions up by UUID — directly, and by joining other tables against it.
The problem
High-frequency lookups by session UUID were doing full table scans. Joins were worse: without an index on the join column, a nested-loop join scans the inner table once per outer row, so the cost multiplies rather than adds.
The index
I ran EXPLAIN on the slow queries and confirmed there was no index on the UUID foreign key. I added a B-tree index — the right structure for equality lookups on a near-unique column, which is the best case for the optimizer.
That alone took about 70% off latency. I also added index and plan checks to our code review checklist for new foreign keys, because the class of bug mattered more than the one instance.
The part nobody assigned
As part of an infrastructure optimization push, I kept digging into how the UUIDs were stored: 36-character strings, where 16 bytes of raw binary would do. That's less than half the size, and two things follow from it:
- Cheaper comparisons. InnoDB compares raw bytes instead of doing collation-aware string comparison.
- Better index efficiency. Pages are 16KB. Smaller entries mean more per page, a shallower tree, fewer page reads, and more of the index resident in the buffer pool.
There's a subtler multiplier: an InnoDB secondary index leaf stores the indexed column plus the primary key. A fat key silently inflates every index it appears in.
Proving it
Rather than argue from first principles, I built a benchmark comparing execution time and storage footprint for string versus binary UUIDs, then converted at the query boundary with UUID_TO_BIN() and BIN_TO_UUID().
What it cost
Conversion on every insert and read is added complexity, and ad-hoc queries in a SQL client return unreadable binary. Worth it at our data volume; not worth it on a small table.
Outcome
- ~70% lower latency from the index
- Storage on those columns more than halved by the binary change
- A review practice that stopped the same class of issue recurring
What I learned
Two wins came from one change — storage and latency — but only because I measured instead of assuming. And a change is more credible when you're honest about what it costs.