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 |