MySQL Schema Checks

This page is original PmaControl documentation. It keeps a practical index structure for MySQL optimization work, without copying third-party content or depending on an external product.

Objective

Schema checks look for structures that are expensive in production: missing indexes, oversized columns, missing primary keys, historical MyISAM tables, and unused indexes.

What to Check

1. InnoDB tables without a primary key.
2. Duplicate or highly redundant indexes.
3. VARCHAR or TEXT columns used in overly wide indexes.
4. Join columns with different types or collations.
5. Large tables without an archiving strategy.
6. Missing foreign keys where business integrity requires them.

Useful Queries

SELECT table_schema, table_name
FROM information_schema.tables t
WHERE engine = 'InnoDB'
  AND table_schema NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys')
  AND NOT EXISTS (
      SELECT 1
      FROM information_schema.table_constraints c
      WHERE c.table_schema = t.table_schema
        AND c.table_name = t.table_name
        AND c.constraint_type = 'PRIMARY KEY'
  );

Key Point

A relevant schema check links every anomaly to a query, an execution plan, or a measurable operational cost.

Inventory size, engines and ownership

Start with business-critical tables and the queries that access them. Record table purpose, retention policy, write rate and maintenance window. Schema metadata identifies candidates, but does not prove a performance problem. For InnoDB, reported row counts and sizes are estimates; use them for ranking rather than exact accounting. Avoid expensive full-table counts solely to make an inventory look precise.

SELECT table_schema, table_name, engine, table_rows,
       ROUND(data_length / 1024 / 1024, 1) AS data_mib,
       ROUND(index_length / 1024 / 1024, 1) AS index_mib
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('mysql', 'sys', 'performance_schema',
                           'information_schema')
ORDER BY data_length + index_length DESC
LIMIT 20;

Review index candidates against real workload

SELECT table_schema, table_name, redundant_index_name,
       dominant_index_name
FROM sys.schema_redundant_indexes
WHERE table_schema NOT IN ('mysql', 'sys');

SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('mysql', 'sys');

These MySQL sys views require the schema to be installed and relevant instrumentation to be available. An unused index is unused only in the observed workload: include reporting, month-end jobs, replica reads and failover roles. Check uniqueness and foreign-key dependencies before removal. A prefix overlap is a review candidate, not an automatic deletion instruction. In MySQL 8+, eligible indexes can first be made invisible in a controlled test, but they still incur write maintenance.

Check access paths and column compatibility

SELECT table_name, column_name, column_type, is_nullable,
       character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'app'
ORDER BY table_name, ordinal_position;

EXPLAIN FORMAT=JSON
SELECT id, created_at
FROM app.orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Replace app.orders and its columns with an actual representative query. Inspect possible keys, chosen key, estimated rows, residual filters and sorting. For this example, an index starting with (customer_id, created_at) is a candidate to test, not a universal prescription. Match join-column types, signedness, character sets and collations. Implicit conversion or functions on indexed columns can change the access path. Verify the plan with realistic parameter distributions, including large tenants.

Classify structural findings

Turn a finding into a controlled migration

• Capture SHOW CREATE TABLE, dependent queries, foreign keys and the original plan.
• Estimate temporary disk, build time, metadata-lock waits and replica impact for the exact DDL and version. Online DDL can still wait for metadata locks.
• Test on representative data; compare read latency, write overhead, size and result correctness.
• Plan rollback explicitly. Rebuilding a dropped index takes time; a schema rollback may require data conversion or restore.

Continue the investigation

• Connect each finding to performance tuning and record it in the evaluation page.

Official references

• MySQL redundant indexes
• MySQL unused indexes
• MySQL online DDL