MySQL: Script to find Index Usage Statistics of a Database

In this post, I am sharing a script to find the Index Usage Statistics of MySQL.
This is a handy script for DBAs for finding the usage of indexes, and they can also take a decision to remove the unused indexes from the database server.

Below are two different scripts:

First, using SHOW INDEX:

1	SHOW INDEX FROM TableName FROM DatabaseName

Second, using INFORMATION_SCHEMA.STATISTICS table:

1 2 3 4 5 6 7 8 9

SELECT     TABLE_NAME     ,INDEX_NAME     ,SEQ_IN_INDEX     ,COLUMN_NAME     ,CARDINALITY     ,INDEX_TYPE     FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema = 'DatabaseName'

You can take a decision basis on the SEQ_IN_INDEX column data.