What a MySQL schema review checks
A MySQL schema review works on table definitions, not on runtime output. Nothing has to be slow yet and nothing has to have broken yet. The definition on its own already tells you whether a table has a usable primary key, whether two of its indexes do the same job, whether a foreign key can be enforced at all, and whether a row can physically fit in an InnoDB page.
That makes it the cheapest review you can run. A mysqldump --no-data takes seconds,
carries no application data, and can be handed to a tool or a colleague without a security
conversation first. What comes back are the problems that get expensive later, once the table has
grown and the application depends on its shape.
Schema Advisor runs every one of its checks, across 8 categories, on every table it finds. Each finding carries a severity, the evidence taken from your own definition, and a fix. Where the definition alone cannot settle a question, the finding says so and gives you the query to run on the server.
Alongside the checks, the report lists column type guidelines for columns whose names suggest a common kind of data, such as an IP address, a UUID, a date or an amount of money, stored in a type that tends to cost more than it should. Those are general guidance, not findings: a name is not proof of what a column holds, so they are never counted and never change a rating.
MySQL table design best practices this tool checks
None of the rules below are exotic. They are the things that are easy to get wrong early and painful to change once the table holds a few hundred million rows.
Primary key design
Every InnoDB table should have an explicit primary key. Without one, InnoDB generates a hidden 6-byte row ID, and the counter behind it is shared across the instance rather than being per table. A table with no primary key also cannot take part in Group Replication, and row-based replication has to scan to find each row it needs to change.
Width matters too, because every secondary index on an InnoDB table stores a copy of the primary
key. A wide primary key is paid for again in each secondary index. A UUID held in
CHAR(36) is the usual version of this: 36 bytes of character data repeated in every
secondary index, in an order with no relationship to insert order. Schema Advisor flags wide primary
keys and UUIDs stored as text, and reports AUTO_INCREMENT headroom when the declared
type is close to running out.
Index design and redundant indexes
Two kinds of waste turn up constantly. A duplicate index is an exact repeat of another index on the same columns in the same order. A redundant index is one whose columns are already the leading prefix of a wider index, so the wider index can serve both. Both cost write time and buffer pool space on every insert, update and delete, and neither buys a read that another index does not already serve.
A third case is quieter: a secondary index that repeats the primary key, or a unique key declared on exactly the columns already in the primary key. InnoDB enforces that uniqueness already, so the extra key is pure overhead. Schema Advisor also flags nullable columns inside a unique key, since NULL does not compare equal to NULL and the key will not do what most people assume, and indexes on long text columns, where a prefix length is usually what was meant.
Foreign key compatibility
A foreign key only works if the child column and the parent column genuinely match. The type has to
match, the signedness has to match, and for string columns so do the character set and collation.
INT referencing BIGINT, or INT UNSIGNED referencing
INT, is a mismatch that either refuses to create or quietly costs you on every
check.
These are resolved across tables, so the parent table has to be in the same input for the comparison
to be made. ON DELETE CASCADE is reported as well. It is not wrong, but it deserves a
deliberate decision, because one delete can travel further than the person running it expects.
InnoDB row size limits
MySQL has a hard limit of 65,535 bytes per row, and it applies whatever the storage engine. InnoDB adds a tighter one on top: roughly half a page, which works out at about 8,126 bytes with the default 16KB page size. Past the hard limit the table will not be created at all. Past the InnoDB limit it also fails, unless the row format lets enough columns be pushed off-page.
The arithmetic is not obvious, which is exactly why it is worth automating. BLOB and
TEXT columns contribute only a 9 to 12 byte pointer to the row, while a single
VARCHAR(255) in utf8mb4 can contribute over a thousand bytes. Schema Advisor works the
row size out from your declared columns and character sets and reports the two limits separately.
Character sets and collations
utf8mb3, and the old utf8 alias that pointed at it, cannot store characters outside the
Basic Multilingual Plane. No emoji, no supplementary characters, and the failure shows up as a
truncated or rejected insert rather than as something anyone notices in testing. utf8mb4 is the
default from MySQL 8.0 onward and is what new tables should use.
Mixed character sets inside one schema are the other half of this. They stop an index being used on a join between two differently encoded columns, and they are a common reason a foreign key cannot be created in the first place.
Column type guidelines for common data
Some kinds of data turn up in almost every application, and so do the same type choices for them: an
IP address in a VARCHAR(45), a UUID in a CHAR(36), a phone number in a
BIGINT. The table below is the usual guidance for each. It is general guidance, not a rule:
the right type depends on what the column actually holds, which a definition cannot show.
When Schema Advisor sees a column whose name suggests one of these, in a type from the second column, it lists it under Column type guidelines in the report with a read-only query to look at the values first. Those entries are never counted as findings and never change a table's rating.
| Data | Often stored as | Usual choice | Why | Source |
|---|---|---|---|---|
| IP addresses | VARCHAR(15), VARCHAR(45) | MySQL: VARBINARY(16) with INET6_ATON() and INET6_NTOA(), 4 bytes for IPv4 and 16 for IPv6. IPv4 only: INT UNSIGNED with INET_ATON(). MariaDB: INET6 (10.5 and later) or INET4 (10.10 and later). | Smaller, an invalid address does not convert, and ranges and subnets compare directly. | MySQL docs, MariaDB INET6, INET4 |
| UUIDs (other than the primary key, see above) | CHAR(36) | MySQL: BINARY(16) with UUID_TO_BIN(value, 1) and BIN_TO_UUID(value, 1), the same flag both ways. MariaDB 10.7 and later: UUID. | 36 characters become 16 bytes, in the column and in every index on it. | MySQL docs, MariaDB UUID |
| Hashes and checksums | CHAR(32), CHAR(64) in utf8mb4 | BINARY(16), BINARY(20) or BINARY(32) with UNHEX() and HEX(), or the hex text in CHARACTER SET ascii. | utf8mb4 budgets 4 bytes per character in an index, so a 64-character hash counts as 256 bytes. | Standard practice |
| MAC addresses | VARCHAR(17) | BINARY(6), read back with HEX(). | Six bytes, and one spelling instead of mixed case, colons and dashes. | Standard practice |
| Yes or no flags | CHAR(1) 'Y'/'N', VARCHAR, INT | TINYINT(1) NOT NULL DEFAULT 0 holding 0 or 1 (BOOLEAN is another name for it). | One byte and one meaning. | Standard practice |
| Dates and times | VARCHAR, INT Unix seconds | DATE or DATETIME. TIMESTAMP stops at 2038-01-19. Unix seconds on purpose: BIGINT. | Dates kept as text sort and compare as text, and converting them in a query keeps an index from being used. | Standard practice |
| Money | VARCHAR, FLOAT, DOUBLE | DECIMAL with the currency's decimals, for example DECIMAL(15,2). | Exact, and sums and sorts as a number. | Standard practice |
| Phone numbers | INT, BIGINT | VARCHAR(20). International numbers (E.164) have at most 15 digits plus a leading +. | An integer drops leading zeros and the +. | Standard practice |
| Postal codes | INT | VARCHAR(10) or so. | An integer drops leading zeros and cannot hold letters. | Standard practice |
| Country, currency and language codes | VARCHAR(255) in utf8mb4 | CHAR(2) for ISO 3166-1 country codes, CHAR(3) for ISO 4217 currency codes, a short VARCHAR for language tags, all in CHARACTER SET ascii. | Short, fixed lists: small indexes and no "India", "india", "IND" variants. | Standard practice |
| JSON documents | TEXT, LONGTEXT | MySQL: JSON, validated on write and stored in a binary form. MariaDB: JSON, another name for LONGTEXT that adds a JSON_VALID check automatically. | Malformed documents are refused on the way in, not discovered on the way out. | MySQL docs, MariaDB JSON |
| Latitude and longitude | FLOAT, VARCHAR | DECIMAL(9,6). For distance or area searches, POINT with SRID 4326 and a SPATIAL index. | Exact values that compare as numbers. | Standard practice |
| Email addresses | TEXT | VARCHAR(255). | Can be indexed in full and declared UNIQUE. | Standard practice |
| Years | VARCHAR(4), INT | YEAR (1 byte, 1901 to 2155), or SMALLINT UNSIGNED for years outside that range. | One or two bytes, and it compares as a year. | MySQL docs |
| Created and updated times | DATETIME with no default | DEFAULT CURRENT_TIMESTAMP, plus ON UPDATE CURRENT_TIMESTAMP on the updated column. Both work on DATETIME as well as TIMESTAMP. | The server keeps them right when a code path forgets. Leave them if the application sets a business time on purpose. | MySQL docs |
Schema conditions that block a MySQL upgrade
Most upgrade checklists concentrate on configuration: variables deprecated, renamed or removed at the target version. Schema problems are the other half, and they tend to stop the upgrade rather than produce a warning you can read later.
Reserved words are the one that catches people. A word that is a perfectly legal identifier today
may be reserved at the version you are moving to. MySQL 8.0 made a batch of new words reserved,
including RANK, ROW_NUMBER, GROUPS and WINDOW,
and 8.4 added QUALIFY and TABLESAMPLE. A column named rank is
fine now and a syntax error afterwards, in every query that does not quote it.
Schema Advisor checks against the reserved word list for the target version you pick, not for your current one, and marks those findings as upgrade blockers so they sort to the top. It also reports identifiers longer than 64 characters, tables still on MyISAM or an older InnoDB row format, and partitioning on an engine with no native partitioning support, which MySQL 8.0 removed.
How to collect your schema
Definitions only, no rows:
mysqldump --no-data --skip-add-drop-table --routines=FALSE -u user -p dbname > schema.sql
--no-data is what keeps application data on your server.
--routines=FALSE leaves stored programs out, since this tool reviews tables. To review
only part of a schema, name the tables after the database name. A single CREATE TABLE
pasted straight into the box works just as well as a whole dump.
The account doing the dump needs SELECT on the tables involved, and
SHOW VIEW if the schema contains views. If you already created a read-only user for the
configuration collection guide, check that it also has
SELECT on the schema you want to review.
Frequently asked questions
What is MySQL Schema Advisor?
Schema Advisor is the table design tool in MySQL Advisor. It reads CREATE TABLE statements or a mysqldump --no-data file and reviews each table for primary key design, duplicate and redundant indexes, foreign key compatibility, InnoDB row size, character sets, and common design antipatterns. It parses the SQL, it never executes it.
What does Schema Advisor check?
Checks across 8 categories: tables without a primary key, wide or UUID-as-text primary keys, AUTO_INCREMENT headroom, duplicate and redundant indexes, unique keys that repeat the primary key, nullable columns in unique keys, indexed long text columns, foreign keys whose type, signedness or character set does not match the parent, non-InnoDB engines and old row formats, InnoDB row size against the 65,535 byte and 8,126 byte limits, utf8mb3 and mixed character sets, float for money, zero date defaults, oversized ENUM lists, the 2038 TIMESTAMP boundary, tables that are almost entirely text columns, reserved words at the target version, and identifiers over 64 characters.
Do I need to send my data to review a schema?
No. Schema Advisor only needs definitions. mysqldump --no-data produces the CREATE TABLE statements with no rows, so no application data leaves your server. The input is parsed and discarded once the response is sent, and nothing is written to disk.
Which MySQL and MariaDB versions are supported?
Choose MySQL 8.0, 8.4 LTS or 9.7 LTS as the target version, and the version-specific checks, such as reserved words, are measured against that target rather than against your current version. Tick the MariaDB box if the dump came from MariaDB, since a few checks do not apply there.
Does Schema Advisor connect to my database?
No. There is no agent, no SSH and no credentials. You paste or upload the definitions and the review runs on that text. Where a finding needs something a definition cannot show, such as what is actually stored in a BINARY(16) column, it hands you the query to run yourself.