Spotting missing indexes for MariaDB & MySQL

missing_indexes.sql

SELECT 
  t.TABLE_SCHEMA, 
  t.TABLE_NAME, 
  c.COLUMN_NAME, 
  IFNULL(kcu.CONSTRAINT_NAME, 'Not indexed') AS `Index`, 
  kcu.ORDINAL_POSITION, 
  kcu.POSITION_IN_UNIQUE_CONSTRAINT, 
  kcu.REFERENCED_TABLE_SCHEMA, 
  kcu.REFERENCED_TABLE_NAME, 
  kcu.REFERENCED_COLUMN_NAME,
  CASE WHEN s.NON_UNIQUE = 0 THEN 'Unique' ELSE 'Non-unique' END AS `Index Type`
FROM 
  information_schema.TABLES t 
  INNER JOIN information_schema.`COLUMNS` c 
    ON c.TABLE_SCHEMA = t.TABLE_SCHEMA 
    AND c.TABLE_NAME = t.TABLE_NAME 
    AND c.COLUMN_NAME LIKE '%Id' 
  LEFT JOIN information_schema.`KEY_COLUMN_USAGE` kcu 
    ON kcu.TABLE_SCHEMA = t.TABLE_SCHEMA 
    AND kcu.TABLE_NAME = t.TABLE_NAME 
    AND kcu.COLUMN_NAME = c.COLUMN_NAME 
  LEFT JOIN information_schema.STATISTICS s
    ON s.TABLE_SCHEMA = t.TABLE_SCHEMA 
    AND s.TABLE_NAME = t.TABLE_NAME 
    AND s.COLUMN_NAME = c.COLUMN_NAME 
    AND s.INDEX_NAME = kcu.CONSTRAINT_NAME 
WHERE 
  t.TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql');