To avoid making code changes every time we have a database change, we just use the SHOW FIELDS (Alias of SHOW COLUMNS) to get all of the columns for Inserts. We call this multiple times for multiple tables in various scripts.
Recently we have discovered speed issues so we started to look at the temp table disk report and found that the SHOW FIELDS FROM 'sometable' all had 100% hits to the disk temp table.
I've tried turning up our query cache size (yes it is enabled), and I've disabled the meta calculations 'SET GLOBAL innodb_stats_on_metadata = OFF;'
We are still getting 100% disk temp table hits on all of these SHOW queries. Is there an easy way to fix these without a code change? We would like to still do our inserts without updating the columns in the code, and the SHOW FIELDS call is part of a framework and we would like to avoid rewriting it if possible. Does anyone know of a way to optimize the SHOW COLUMNS command or index it in a way that will speed these up?