FindMissingIndexes.sql

/*
Purpose:
- Find columns named like foreign keys (`*_id`) that are not the leading column of an index.
- Generate CREATE INDEX statements for human review.

Safety:
- Read-only and generates commands only; it does not create indexes.
- Naming is only a heuristic. Validate each candidate against queries and constraints.

Requirements:
- MySQL 8.0 or later.

Customization:
- Change the column-name predicate if your schema uses a different convention.
*/

SELECT
    columns.TABLE_SCHEMA AS table_schema,
    columns.TABLE_NAME AS table_name,
    columns.COLUMN_NAME AS candidate_column,
    CONCAT(
        'CREATE INDEX `IX_',
        REPLACE(columns.TABLE_NAME, '`', '``'),
        '_',
        REPLACE(columns.COLUMN_NAME, '`', '``'),
        '` ON `',
        REPLACE(columns.TABLE_NAME, '`', '``'),
        '` (`',
        REPLACE(columns.COLUMN_NAME, '`', '``'),
        '`);'
    ) AS command_to_review
FROM information_schema.COLUMNS AS columns
WHERE columns.TABLE_SCHEMA = DATABASE()
  AND columns.COLUMN_NAME LIKE '%\_id' ESCAPE '\\'
  AND NOT EXISTS
  (
      SELECT 1
      FROM information_schema.STATISTICS AS indexes
      WHERE indexes.TABLE_SCHEMA = columns.TABLE_SCHEMA
        AND indexes.TABLE_NAME = columns.TABLE_NAME
        AND indexes.COLUMN_NAME = columns.COLUMN_NAME
        AND indexes.SEQ_IN_INDEX = 1
  )
ORDER BY columns.TABLE_NAME, columns.ORDINAL_POSITION;