Scanning a schema for design and maintenance problems.
The Diagnostics subview of Schema Structure checks a schema for common problems, and makes recommendations for you to fix them.
Only you can reallyknow whether something is a problem or not, but using Diagnostics when you inherit a schema from another engineer/source, before a release, or periodically on a busy server, can help point out some issues you might not have otherwise noticed or thought of.
The list on the left groups the checks by category, with the number of issues each found. Select a check to see its explanation and the objects it flagged, with details such as the columns or indexes involved.
Each check has a severity:
| Severity | Meaning |
|---|---|
| Critical (red) | Likely to cause failures or data problems. Fix soon. |
| Warning (yellow) | Costs performance or correctness in some situations. |
| Advisory (blue) | Worth reviewing, but may be intentional. |
Scanning only reads the server's catalog and statistics. Nothing is changed, and each issue is yours to fix.
| Check | Severity |
|---|---|
| Tables Missing Primary Key | Critical |
| Duplicate Indexes | Warning |
| Redundant Indexes | Advisory |
| Tables with Non-Default Charset | Warning |
| Columns with Non-Default Collation | Advisory |
| Mismatched FK Collations | Warning |
| Tables and Columns using Older utf8 | Warning |
| Auto Increment Near Limit | Critical |
| Fragmented Tables | Advisory |
| Non-InnoDB Tables | Warning |
| Floating Point Currency | Advisory |
| Columns Missing a Foreign Key | Advisory |
| Legacy Design | Advisory |
| Broken Views | Critical |
| Objects with a Missing Definer | Critical |
The check: Finds tables with no primary key.
Without one, replication and updates or deletes on those tables are slow or unreliable.
Recommendation: Add a primary key, typically an AUTO_INCREMENT integer column.
The check: Finds two indexes on the same columns in the same order.
Both must be updated on every write, so the copy wastes storage and slows writes, with no query benefit.
Recommendation: Drop one of the two.
The check: Finds a non-unique index that is just the leading part of a longer index.
The longer index already serves the same queries, so the shorter one only adds write cost.
Recommendation: Drop the shorter index, after confirming no query depends on it by name (such as an index hint).
The check: Finds tables whose collation differs from the schema's default.
Mixed collations cause surprising comparisons and can prevent an index from being used.
Recommendation: If it wasn't intentional, convert the table to the schema default with ALTER TABLE ... CONVERT TO CHARACTER SET.
The check: Finds columns whose collation differs from their table's.
This has the same problems as above, but within a single table.
Recommendation: If it wasn't intentional, change the column to the table's collation.
The check: Finds foreign keys whose two columns use different collations.
Joins on the relationship must convert values at query time, which loses the use of the index.
Recommendation: Give both columns the same collation.
The check: Finds tables and columns still using 3-byte utf8 (utf8mb3).
They can't store emoji or other 4-byte characters.
Recommendation: Convert to utf8mb4.
The check: Finds AUTO_INCREMENT counters at 70% or more of their column type's maximum.
Once the limit is reached, inserts fail.
Recommendation: Change the column to a larger integer type, such as BIGINT UNSIGNED, before it runs out.
The check: Finds tables where 50 MB or more (and 25% or more) of the table's file is unused space.
The wasted space costs disk and can slow scans.
Recommendation: Run OPTIMIZE TABLE to reclaim it. Note that InnoDB doesn't report space left inside partly empty pages, so this check can miss the most common case.
The check: Finds tables using MyISAM, MEMORY, or another engine besides InnoDB.
These engines lack transactions, row locks, and crash recovery, and MEMORY tables lose their data when the server restarts.
Recommendation: Convert the table to InnoDB, unless the engine is deliberate.
The check: Finds FLOAT or DOUBLE columns with names that look like money, such as price, total, or tax.
Binary floating point silently rounds decimal amounts.
Recommendation: Change the column to DECIMAL.
The check: Finds columns like customer_id that match a table and its primary key's type but have no foreign key.
Without a constraint, rows can point at records that don't exist.
Recommendation: Add a foreign key if the column refers to that table. This is a guess based on naming, so ignore columns that refer to outside systems.
The check: Finds TIMESTAMP columns, COMPACT or REDUNDANT row formats, and ZEROFILL.
TIMESTAMP stops at the year 2038, the old row formats limit index and value storage, and ZEROFILL is deprecated.
Recommendation: Use DATETIME, the DYNAMIC row format, and pad numbers in your application (or with LPAD) instead of ZEROFILL.
The check: Finds views that reference a table, column, or function that no longer exists.
Any query that uses the view fails.
Recommendation: Redefine the view to use what exists now, or drop it if it's no longer needed.
The check: Finds views, routines, triggers, and events whose defining user no longer exists. This needs read access to mysql.user.
Using them fails. It often happens after restoring a dump onto another server.
Recommendation: Recreate the object with a definer that exists, or create the missing user.
| Check | Severity |
|---|---|
| Tables Missing Primary Key | Critical |
| Invalid Indexes | Critical |
| Sequences Near Max Value | Critical |
| Transaction ID Wraparound Risk | Critical |
| Unindexed Foreign Key Columns | Warning |
| Duplicate Indexes | Warning |
| Redundant Indexes | Advisory |
| Never-used Indexes | Advisory |
| High Dead Tuple Ratio | Warning |
| Tables Not Vacuumed Recently | Warning |
| NOT VALID Constraints | Warning |
| TIMESTAMP WITHOUT TIME ZONE | Advisory |
The check: Finds tables with no primary key.
Without one, individual rows can't be reliably identified, which complicates updates, deletes, and logical replication.
Recommendation: Add a primary key.
The check: Finds indexes left invalid, usually by a failed CREATE INDEX CONCURRENTLY.
They're maintained on every write but never used for queries.
Recommendation: Drop the index and create it again.
The check: Finds sequences approaching their maximum value. See Sequences.
Once a sequence reaches its maximum, inserts that use it fail.
Recommendation: Change the column and sequence to a larger type, such as bigint, before it runs out.
The check: Finds tables nearing the freeze age.
If wraparound is reached, the server stops accepting writes to protect data.
Recommendation: Run VACUUM FREEZE on the table.
The check: Finds foreign key columns with no index.
Deleting or updating a row in the referenced table has to scan the whole referencing table, and joins on the relationship are slow.
Recommendation: Create an index on the foreign key column.
The check: Finds indexes on exactly the same columns as another.
Both must be updated on every write, wasting storage and slowing writes, with no query benefit.
Recommendation: Drop one of the two.
The check: Finds indexes whose columns are a leading prefix of another index.
The longer index already serves the same queries, so the shorter one only adds write cost.
Recommendation: Drop the shorter index.
The check: Finds indexes with no scans since statistics were last reset.
An unused index costs storage and write time for no benefit. It may still be needed for a rarely-run query, or for a unique constraint.
Recommendation: Check Index Usage, then drop the index if it's truly unneeded.
The check: Finds tables with a large share of dead rows.
It's a sign of bloat and of autovacuum falling behind, which wastes space and slows queries.
Recommendation: Run Vacuum, and consider tuning autovacuum for the table.
The check: Finds tables with significant changes that haven't been vacuumed in over 7 days.
Dead rows accumulate and the planner's statistics grow stale.
Recommendation: Run Vacuum and Analyze, and check that autovacuum is running.
The check: Finds constraints that were never checked against existing rows.
Existing data may violate them, and the planner can't rely on them.
Recommendation: Run ALTER TABLE ... VALIDATE CONSTRAINT.
The check: Finds timestamp columns with no time zone.
The stored value is ambiguous, which causes bugs when an application or its users span time zones.
Recommendation: Change the column to timestamptz, unless it truly holds local wall-clock time.
Fix maintenance issues with Vacuum and Analyze in Table Operations.
