MySQL Workbench secretly running SHOW INDEX after every query A few days ago, I revised some innodb_stats_* parameters and ran mysqlcheck --analyze --all-databases on some Percona 5.5.38 servers. Now, whenever I execute any simple query in MySQL Workbench, it seems to execute a background query of SHOW INDEX ON t1;  Needless to say, this caused some issues when it blocked updates to the table on production servers. 

I have ascertained these things:


it happens with any account with sufficient priviledges
it doesn't happen from the command line
it happens for other users
it happens even after I upgrade to 6.2.4 and delete all the settings
it DOESN'T happen against my Percona 5.6 database
SHOW INDEX starts about 5 seconds AFTER my query


I haven't been able to find any explanation for this behavior on the net, so wanted to see if any experienced DBA's have run into this. 

Current stats related settings:


innodb_stats_auto_update 0
innodb_stats_method  nulls_equal
innodb_stats_on_metadata ON
innodb_stats_sample_pages    8
innodb_stats_update_need_lock    1
innodb_use_sys_stats_table   OFF


Thanks for any pointers anyone can provide. 

UPDATE 
After looking through the source code, it turns out that the SHOW INDEX happens for every query on a single table. It checks to see if the selected columns contain a primary key or other unique index, so it can decide whether you can edit the data. 

The source code contains this comment: 

// XXX this can be slow because of the I_S queries, depending on the server

I went to see why my INFORMATION_SCHEMA was slow, and found a helpful post from Percona

It turns out that innodb_stats_on_metadata had gotten turned on during this process, which causes MySQL to recalculate the statistics every time INFORMATION_SCHEMA is accessed. So I just turned it back off. 

What a nightmare.
Scanning the source code of MySQL Workbench you can see a few places where SHOW INDEX is sent out. The application needs to run quite a number of queries for its tasks, e.g. the index view or the table editor. Some of them are only active when you open a view from the management section. Others, like the SQL editor need a SHOW INDEX to find info about PK columns which is needed to decide if a resultset returned for a query can be edited.