MySQL Bugs: #106528: list of unused indexes on sys.schema_unused

Bug #106528	list of unused indexes on sys.schema_unused_indexs
Submitted:	21 Feb 2022 14:34	Modified:	23 Feb 2022 20:14
Reporter:	Izzy Fayon	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: SYS Schema	Severity:	S2 (Serious)
Version:	5.7.35	OS:	Ubuntu (18.4)
Assigned to:	MySQL Verification Team	CPU Architecture:	ARM

Description:
I'm using MySQL community edition on Ubuntu 18.04 with 8CPU and 2TB data disk for long time, uptime 652 days. Database serving requests from web server. Database server and Web Server installed on different VM's. It is important to mention that performance was great and server was acting as expected until indexes appairing on sys.schema_unused_indexs removed. 
After deleting the index from table, CPU got 100% and also observed heavy IO from iotop command. In addition, SHOW PROCESSLIST command gave me long acting SELECT statements on mentioned table (time 1000+) and state of "creating sort index". Due to exclusive lock on the table i wasn't able to recreate the index on that table, so i did it on one of the slave machines which is not requested by the web server and done switchover - slave (with recreated indexes becomes as master) and issue fixed. I expected that when some index is listed on sys.schema_unused_indexs DMV as unused especially after so long up-time, removing object (index) is safe. I would like to get some explanation why is happened and if i can trust to sys.schema_unused_indexs.

How to repeat:
create table and some indexes and select data from table and check explain plan

Hi

SYS database contains useful views giving you often useful info but it is not 100% and depends on the other things. You cannot just kill the indexes you see int unused_indexs view. What you should do is utilize the modern function to disable (temporary) indexes and monitor performance and drop them only if you see no performance changes... and also - one by one :D

For further help about optimizing mysql server contact either our support team or our community forum for help.

Thanks