Database

Primary Key Advisor

Reviews the primary key on each table and says what it will cost you.

Loading the tool…

Processing happens locally in your browser. What you paste or load is processed by this page and is not uploaded to a server. Nothing is stored unless you use a control that says it stores something, and you can clear anything this site has kept from the privacy page.

How to use this tool

  1. Paste the CREATE TABLE statements for the tables you want reviewed.
  2. Pick the engine — a wide key costs considerably more on InnoDB than elsewhere, and the advice changes accordingly.
  3. Select Review the keys.
  4. Deal with any table reported as having no primary key first; that is the only finding here that breaks correctness rather than performance.

What primary key advisor does

The primary key is the one decision on a table that is genuinely hard to change later, because everything else points at it. A random key such as a version 4 UUID scatters inserts across the whole index instead of appending to the end, so page splits never stop and the cache holds a fraction of what it would. A wide key is copied into every secondary index on the table under InnoDB and into every clustered index lookup elsewhere. A composite key can be used for a lookup on its first column and not on its last. And a table with no key at all cannot be updated a row at a time with confidence, replicated reliably, or referenced by anything.

This page reads your CREATE TABLE and reports on each table in turn: the key, its width in bytes, whether it is monotonic or random, what its width does to the secondary indexes it will carry, whether the column order of a composite key matches what you are likely to filter on, and whether any key column has been left nullable. Tables with no key get the nearest candidates the DDL offers. Choose the engine before running it — the consequences of a wide key are considerably worse on InnoDB than elsewhere, and the report says so.

Frequently asked questions

A random one is, at scale. Version 4 UUIDs land at arbitrary points in the index, so inserts touch pages all over the file instead of the hot end, page splits are constant and the cache holds far less of the index than it would. Two fixes work: store it as a 16-byte binary rather than a 36-character string, and use a time-ordered form such as UUIDv7 so that inserts append. At small scale none of this matters.

Very much. The key index can serve a lookup on the first column alone, on the first two together, and so on — but not on the last column by itself. Put the column you filter on most often first. If you genuinely need to look up by the later column too, that is a separate index rather than a different key order.

Several things at once. You cannot reliably update or delete exactly one row when two are identical, row-based replication has nothing to match on and can be extremely slow, nothing can declare a foreign key to the table, and on InnoDB the engine invents a hidden six-byte key anyway — you pay for one without being able to use it.

This tool does not take a side, because the trade is real. A natural key removes a join and a column; a surrogate key survives the day the business changes what an order reference looks like. What it does report is the consequence you can measure: how wide the key is, and what that width costs in every secondary index that has to carry it.