Index All Foreign Key Columns

Unlike PostgreSQL, MySQL/InnoDB requires indexes on foreign key columns. Missing indexes cause slow JOINs.

Why This Matters

InnoDB automatically creates indexes on foreign key columns if missing, but these auto-indexes may not be optimal. Explicit indexes ensure proper query planning.

Related Rules

Use Online DDL for ALTER TABLE

Performance

CRITICAL

Use utf8mb4 Instead of utf8

Architecture

MEDIUM

Avoid SELECT * in Production Queries

Performance

MEDIUM

Catch this automatically on every PR

BeforeMerge scans your pull requests against this rule and 3+ others. Get actionable feedback before code ships.

Join Waitlist Browse All Rules

Why This Matters

InnoDB automatically creates an index on FK columns if one doesn't exist, but:

The auto-created index might not be the optimal composite index
JOINs and cascading deletes still benefit from explicit, well-designed indexes
MariaDB behavior differs slightly from MySQL

Detection

SELECT
  TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME IS NOT NULL
  AND TABLE_SCHEMA = 'mydb'
  AND COLUMN_NAME NOT IN (
    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = 'mydb'
      AND SEQ_IN_INDEX = 1
  );

Fix

CREATE INDEX idx_orders_user_id ON orders (user_id);

Platforms

MySQL 8.0: InnoDB auto-creates indexes, but verify they're optimal
MariaDB: Same behavior as MySQL
PlanetScale: No foreign keys (use application-level enforcement)
TiDB: Supports foreign keys in 7.6+

Index All Foreign Key Columns

Why This Matters

Tags

Related Rules

Catch this automatically on every PR

Why This Matters

Detection

Fix

Platforms