SHOW TABLE STATUS vs SHOW TABLES vs INFORMATION_SCHEMA

I have been battling with some performance issues on one of my Xataface applications of late and I think I just found the cause of periodic slow-downs:

SHOW TABLE STATUS

The output of this command includes useful information about all of the tables in the database including such things as creation time, update time, average row length, and number of rows. In Xataface I primarily use this command to:

  1. Find the modification time of tables so that I can perform smarter caching operations.
  2. Determine if a view exists.

When working exclusively on MyISAM tables, this command is very fast as all of the information returned is cached all the time. However, when we start to throw InnoDB tables (and possibly views .. haven’t looked into it yet) into the mix this command becomes quite slow because much of the data returned needs to be calculated (e.g. the number of rows). I was facing an issue where this command could take upwards of 10 seconds to return when the application hasn’t been used in a while. It would also periodically hang even when the application was in frequent use. Presumably this is because MySQL does some caching of the values in this command, but the cache doesn’t last long.

In addition, InnoDB doesn’t keep track of modification times so despite the fact that it is performing calculations in this command, it still returns NULL for table update times. Which renders the function altogether useless for InnoDB tables.

Xataface has long had a back-up strategy for keeping modification times in InnoDB tables. It keeps its own table of modification times. This table is updated whenever a record is updated from within Xataface. It doesn’t work for updates performed outside of the application. In most cases this is good enough. Even with this back-up solution, the primary method of retrieving table modification times was still the “SHOW TABLE STATUS” mysql command.

Solution #1: Use the Information Schema

My first attempt to rectify the involved a direct query of the INFORMATION_SCHEMA. You can obtain the modification times of all tables in the database with the following query:

select TABLE_NAME as Name, UPDATE_TIME as Update_time from information_schema.tables where TABLE_SCHEMA='my_database_name'

Unfortunately, it turns out that this query is also quite slow (though a bit faster than show table status). My initial tests showed that on a database with about 60 tables it would take about 0.2 seconds to return. Far to slow for an operation that doesn’t contribute directly to the building of the page. What I need is something that returns in less than 0.01 seconds so that it is effectively negligible.

Solution #2: Use SHOW TABLES

SHOW TABLES simply returns a list of the tables in the current database. It doesn’t include any stats or information about those tables other than the table names. It is also very fast (generally returns in 0.00 seconds …. i.e. too small to matter). This is enough information to build my own modification times or check for the existence of a table/view.

References: