MySQL – SHOW commands are expensive

It’s convenient to display information about a table, running status, server configurations. However, it is expensive! A simple “SHOW COLUMNS FROM SomeTable” create a disk-based temporary table!!! The same for “SHOW STATUS LIKE ‘Created_%’”. Lesson: avoid these if you don’t want to see “Created_tmp_disk_tables” and “Created_tmp_tables” counters spinning. It’s not a bug but can mislead in debugging. A patch is available on 5.1.12 (not for 5.0.x though). But the lesson here is to avoid frequent calls to SHOW commands. A better solution is to have MySQL support query caching not just for SELECT… but also for SHOW COLUMNS. These queries do not change that often and can be cached effectively. You could write your own cache but having it done MySQL query caching would be a plus as well.

http://bugs.mysql.com/bug.php?id=10210