← Back

−70%latency

Thirty-six characters too many

~70% lower query latency

initiative · measuring

Zipy kept its session data in MySQL. Every session had a UUID, the long random identifier that looks like 9f8c2e14-..., and a lot of our queries looked sessions up by it.

Those queries were slow. The database was reading every row in the table to find one session, because nothing told it where to look. An index is the equivalent of the index at the back of a book. Without one you read the whole book.

Joins were worse. When you join two tables and the join column has no index, the database searches the second table once for every row it reads from the first. A thousand rows means a thousand searches. The cost multiplies instead of adding up.

The index nobody had added

I analyzed the slow queries, confirmed the column had no index, and added one.

Latency dropped by about 70%. A session ID is almost unique, which is the easiest case for a database to use an index well, so the gain was large and reliable.

Then I added a line to our code review checklist: when someone adds a new link between tables, check whether it needs an index. The single fix mattered less than stopping the same mistake coming back.

Thirty-six characters, sixteen bytes

Nobody asked me to do the next part.

We stored those UUIDs as text, 36 characters long. A UUID is really 16 bytes of binary, and the text version is that same value written out with dashes. So every ID took more than twice the space it needed.

Storing them as binary helped in two ways.

The database compares raw bytes instead of text. Comparing text means following rules about how letters sort, and that work adds up over millions of rows.

The index also gets smaller. MySQL stores an index in blocks of 16KB. Smaller entries mean more of them fit in each block, so the database opens fewer blocks to find a row, and more of the index stays in memory instead of on disk.

I measured it instead of arguing it

I could have made the case from theory. Instead I built a small benchmark that ran the same queries against both versions and compared speed and storage side by side.

Then I converted at the edge of each query, using MySQL's own UUID_TO_BIN() and BIN_TO_UUID() functions, so the rest of the code carried on seeing normal UUIDs. Storage on those columns more than halved.

Where this would be the wrong call

Every insert and read now converts between the two forms, which is more code to keep working. And if you run a quick query by hand, you get unreadable binary back instead of an ID you can copy into a ticket.

At our volume that trade was clearly worth making. On a small table it would not be.