I wrote this handy query to find indexes that can be deleted because they have not been in use. It
queries the performance_schema database for usage on the indexes, and joins on INFORMATION_SCHEMA.TABLES
to see the index size.
Indexes that have zero reads and writes are obvious candidates for removal. They take extra write overhead to keep them
updated, and you can improve performance on a busy server by removing them. You can also free up some disk space
without them. The size column below helps to understand where you have the most opportunity for saving on disk
usage.
mysql> SELECT. OBJECT_NAME, index_name, SUM(INDEX_LENGTH) AS size, SUM(count_star) AS count_star, SUM(count_read) AS count_read, SUM(count_write) AS count_write FROM table_io_waits_summary_by_index_usage JOIN information_schema.TABLES ON table_io_waits_summary_by_index_usage.OBJECT_SCHEMA = TABLES.TABLE_SCHEMA AND table_io_waits_summary_by_index_usage.OBJECT_NAME = TABLES.TABLE_NAME WHERE OBJECT_SCHEMA LIKE 'mydatabase%' GROUP BY object_name, index_name ORDER BY count_star ASC, size DESC LIMIT 20; +------------------------------+---------------------------------+-------------+------------+------------+-------------+ | OBJECT_NAME | index_name | size | count_star | count_read | count_write | +------------------------------+---------------------------------+-------------+------------+------------+-------------+ | transactions | order_id | 42406641664 | 0 | 0 | 0 | | transactions | msku-timestamp | 42406641664 | 0 | 0 | 0 | | transactions | fkTransactionsBaseEvent | 42406641664 | 0 | 0 | 0 | | baseEvent | PRIMARY | 33601945600 | 0 | 0 | 0 | | baseEvent | eventTypeId | 33601945600 | 0 | 0 | 0 | | orders | modified | 20579876864 | 0 | 0 | 0 | | orders | buyerId-timestamp | 20579876864 | 0 | 0 | 0 | | productReports | productAd-date-venue | 8135458816 | 0 | 0 | 0 | | shipmentEvent | id | 7831928832 | 0 | 0 | 0 | | shipmentEvent | eventTypeId | 7831928832 | 0 | 0 | 0 | | historyEvents | timestamp_venue_entity | 4567531520 | 0 | 0 | 0 | | targetReports | venueId-date-targetId | 3069771776 | 0 | 0 | 0 | | productAds | venue-productAd | 1530888192 | 0 | 0 | 0 | | keywords | venue-keyword | 895598592 | 0 | 0 | 0 | | targetingExpressions | venue-target | 215269376 | 0 | 0 | 0 | | targetingExpressions | rType-rValue | 215269376 | 0 | 0 | 0 | | serviceFeeEvent | PRIMARY | 48234496 | 0 | 0 | 0 | | serviceFeeEvent | id | 48234496 | 0 | 0 | 0 | | serviceFeeEvent | eventTypeId | 48234496 | 0 | 0 | 0 | | adGroups | venue-adGroup | 42336256 | 0 | 0 | 0 |