⚡ MySQL Advisor

MySQL Schema Advisor: Table Design Review and Schema Checker

Paste a CREATE TABLE statement, a handful of them, or a whole dump taken with mysqldump --no-data, and get a schema design review, table by table. It checks primary key design, duplicate and redundant indexes, foreign key compatibility, InnoDB row size, character sets, and the schema conditions that stop a major version upgrade, against established MySQL table design best practices.

You get the fix, not a score. Where the definition alone cannot settle a question, the finding says so and hands you the query to run on the server, so nothing in the report is a guess.

Verification code image
Collect it with

Definitions only, no rows. The tool parses the SQL, it never runs it, and nothing is stored on the server after the response is sent.

Version matters for the upgrade checks: a reserved word that is fine on 8.0 may not be on 8.4. Tick the MariaDB box if this came from MariaDB, since a few checks do not apply there.

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.

DataOften stored asUsual choiceWhySource
IP addressesVARCHAR(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 checksumsCHAR(32), CHAR(64) in utf8mb4BINARY(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 addressesVARCHAR(17)BINARY(6), read back with HEX().Six bytes, and one spelling instead of mixed case, colons and dashes.Standard practice
Yes or no flagsCHAR(1) 'Y'/'N', VARCHAR, INTTINYINT(1) NOT NULL DEFAULT 0 holding 0 or 1 (BOOLEAN is another name for it).One byte and one meaning.Standard practice
Dates and timesVARCHAR, INT Unix secondsDATE 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
MoneyVARCHAR, FLOAT, DOUBLEDECIMAL with the currency's decimals, for example DECIMAL(15,2).Exact, and sums and sorts as a number.Standard practice
Phone numbersINT, BIGINTVARCHAR(20). International numbers (E.164) have at most 15 digits plus a leading +.An integer drops leading zeros and the +.Standard practice
Postal codesINTVARCHAR(10) or so.An integer drops leading zeros and cannot hold letters.Standard practice
Country, currency and language codesVARCHAR(255) in utf8mb4CHAR(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 documentsTEXT, LONGTEXTMySQL: 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 longitudeFLOAT, VARCHARDECIMAL(9,6). For distance or area searches, POINT with SRID 4326 and a SPATIAL index.Exact values that compare as numbers.Standard practice
Email addressesTEXTVARCHAR(255).Can be indexed in full and declared UNIQUE.Standard practice
YearsVARCHAR(4), INTYEAR (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 timesDATETIME with no defaultDEFAULT 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.