I am using AWS RDS aurora mysql 5.7 serverless but in RDS I have enabled the performance_schema parameter, I have connected my RDS to mysql workbench version 6.3 in mysql workbench when I am checking the parameter performance_schema it is showing disabled and I have also checked performance_schema has no data in it. I want to check the unused indexes in the schema for that I have to use performance schema.enter image description here

  • No technique gives you a definitive list of "unused indexes". The best any tool can do is give you a list of "indexes that have not been used during the monitoring period". For that reason, I do not recommend using any such tool.
    – Rick James
    Sep 14 at 3:29

1 Answer 1


Please use the sys schema instead

mysql> show create table sys.schema_unused_indexes\G
*************************** 1. row ***************************
                View: schema_unused_indexes
         Create View: CREATE ALGORITHM=MERGE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `schema_unused_indexes` AS select `performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_SCHEMA` AS `object_schema`,`performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_NAME` AS `object_name`,`performance_schema`.`table_io_waits_summary_by_index_usage`.`INDEX_NAME` AS `index_name` from `performance_schema`.`table_io_waits_summary_by_index_usage` where ((`performance_schema`.`table_io_waits_summary_by_index_usage`.`INDEX_NAME` is not null) and (`performance_schema`.`table_io_waits_summary_by_index_usage`.`COUNT_STAR` = 0) and (`performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_SCHEMA` <> 'mysql') and (`performance_schema`.`table_io_waits_summary_by_index_usage`.`INDEX_NAME` <> 'PRIMARY')) order by `performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_SCHEMA`,`performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_NAME`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)


You would have to restart mysql to have a quick clear picture because it is a view that displays indexes that have no events (i.e. they have not been used since the the most recent restart).

SELECT * FROM sys.schema_unused_indexes\G

If you have the slow query log enabled and you do not want to restart mysql, you could also try pt-index-usage. (You are using Aurora so get the slow query log downloaded first.

NOTE : Please check to see if you have enabled "Performance Insights". It is not available in v1 but might be in v2. It may be useless if the stats reset a lot (After all, you are serverless)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.