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.