How can I speed up “show columns” on MySQL?

Question

My application depends on running "show columns" for certain tables. It takes about 60ms to run, whereas all our other queries take under a ms. Querying information_schema directly is even slower.

The database contains about 250 databases, with 100 to 200 tables per database (about 20k tables in total).

• How can I find out why these operations are so slow?
• Is there perhaps some setting I can change to make it run faster, or to cache it SQL-side?

(The application does about 14 such queries per page load - I'm well aware that this legacy code needs to be cleaned up, but looking for possible options while I work on the long-term fix.)

Answer 1

MySQL recalculates table statistics for certain operations that access INFORMATION_SCHEMA tables (SHOW COLUMNS is just a convenient alias for querying INFORMATION_SCHEMA.COLUMNS). Set innodb_stats_on_metadata to be false, which will prevent this recalculation from occurring when you ask for metadata from the table.

SET GLOBAL innodb_stats_on_metadata=0;

and add the following to my.cnf

[mysqld]
innodb_stats_on_metadata = 0

Answer 2

I suggest you create a database that has the INFORMATION_SCHEMA tables (or only those you need) as replicates. Index them appropriately and you will have performance gain.

The problem of syncing between this database and INFORMATION_SCHEMA is tricky though.

You could have a procedure that synchronizes these tables every hour or every 5 minutes (how often is the tables' structure changed?).

Another idea would be to use MySQL Proxy to catch any ALTER TABLE statements (and CREATE and DROP and CREATE INDEX and whatever other statements modify information you need) and then synchronize the replicated information schema after these statements succeed.

---

If you only need the column names and not any other information, like datatype, length or available indexes, you could perhaps replace the use of SHOW COLUMNS with (fast) queries that return 1 row only, with LIMIT 1 or none at all, with either LIMIT 0 or:

SELECT * FROM TableName WHERE FALSE ;

Despite the general advice against the use of SELECT *, this may be a legitimate case where nothing else is useful. (everything else but *, may result in error!)

Answer 3

I like the first suggestion in @yerpcube's answer (+1), but I'd like to propose something

• create another database instance on port 3307
• mysqldump the production database to SQL Text File using the following options:
  • --no-data
  • --routines
  • --triggers
  • --all-databases or --databases followed by a list of databases you want
• Load the SQL Text file into the port 3307 MySQL Instance

Thus, you mysqldump should look as follows:

mysqldump --no-data --routines --triggers --all-databases > ImportFile.sql

That's it. Going forward, all you need to do is connect to this port 3307 database instance and run any schema-related query to your heart's content. Should you know of any table in the production database that changes, just mysqldump the schema from production and reload it again into the port 3307 instance.

WARNING : If you install a mysql instance on the the same machine as production, make absolutely sure you connect to that instance using

mysql -u... -p... -h127.0.0.1 -P3307 < ImportFile.sql

If you execute

mysql -u... -p... -P3307 < ImportFile.sql

It will hose production. So, BE CAREFUL !!!!

An alternative would be to just use a separate DB server.