Some SQL snippets

unused-indexes.sql

/* Find unused MySQL indexes and show their size */

SELECT * FROM (
  SELECT object_schema as db, object_name as tbl, index_name as idx, count_star
  FROM performance_schema.table_io_waits_summary_by_index_usage
  WHERE index_name IS NOT NULL
  AND index_name != 'PRIMARY'
  AND object_schema NOT IN ('mysql', 'performance_schema')
  ORDER BY object_schema, object_name
) as a
LEFT JOIN (
  SELECT database_name as db, table_name as tbl, index_name as idx,
  ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) size_in_mb
  FROM mysql.innodb_index_stats
  WHERE stat_name = 'size' AND index_name != 'PRIMARY'  
) as b USING (db, tbl, idx)
ORDER BY count_star, size_in_mb  DESC