MySQL Workbench – Why SHOW INDEX Runs After Every Query

mysql-workbench

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:

  1. it happens with any account with sufficient priviledges
  2. it doesn't happen from the command line
  3. it happens for other users
  4. it happens even after I upgrade to 6.2.4 and delete all the settings
  5. it DOESN'T happen against my Percona 5.6 database
  6. 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.

Best Answer

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.

Related Question