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.)

  • 1
    out of interest, in what scenario would 60ms be too slow to examine a table's columns? It's not something you should be doing every request
    – Jaitsu
    Aug 8, 2012 at 18:40
  • 1
    What do you mean show columns? Garbing column names from a table or printing a whole column? If its the names.. why don't you just grab it once and store it in your application... if that's not possible why don't you just create another table that holds all the columns based on the table?
    – Florin Stingaciu
    Aug 8, 2012 at 18:41
  • @Jaitsu: No, it isn't something we should be doing, but, that's the way it is. Legacy code. Until I have some time to clean it up and do it properly, I want to see if I can speed it up. I've got about 14 of these that run every page load.
    – Mark
    Aug 8, 2012 at 20:59
  • @FlorinStingaciu: Yes, column names. Putting them into another table might speed things up, but it will get out of sync, which defeats the entire purpose of asking the table directly.
    – Mark
    Aug 8, 2012 at 21:00
  • 1
    @Mat: Not a bad idea. Voted to migrate to dba.
    – Mark
    Aug 9, 2012 at 16:11

4 Answers 4


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

innodb_stats_on_metadata = 0
  • I should have mentioned that I'm actually using MyISAM. Tried setting this anyway, but it didn't yield any benefit.
    – mpen
    Aug 11, 2012 at 1:32
  • Have you considered ALTER TABLE foo ENGINE=InnoDB? :) Is there a good reason for using MyISAM? Aug 11, 2012 at 2:41
  • Legacy reasons mostly, I think. I'm afraid what might happen if I tried that; not sure all the FKs will line up. I will give it some more thought though.
    – mpen
    Aug 11, 2012 at 5:25
  • @AaronBrown +1 for this answer because whoever faces this situation with an all-InnoDB database needs this info. Aug 11, 2012 at 20:42
  • 1
    +1 for putting [mysqld] there. It might be obvious to many that this setting would go under mysqld, but may not be obvious for those who would ask this question. By the way, this sped up SELECT COUNT(*) on one of my information_schema tables to 6 seconds from over a minute. Still slow, but a huge improvement. Jul 23, 2013 at 21:46

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:


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!)


In this particular case, I think the INFORMATION_SCHEMA is a red herring. From my own tests of SHOW COLUMNS performance, the innodb_stats_on_metadata variable doesn't seem to make any difference on either MyISAM or InnoDB tables.

However, from the MySQL 5.0 manual...

Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:


  • The SHOW COLUMNS and The DESCRIBE statements use BLOB as the type for some columns, thus the temporary table used for the results is an on-disk table.

This seems to have been removed from the manual as of MySQL 5.5, but still appears to apply in that version...

mysql> SHOW VARIABLES LIKE 'version';
| Variable_name | Value                   |
| version       | 5.5.41-0ubuntu0.14.04.1 |
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Created_tmp_disk_tables';
| Variable_name           | Value |
| Created_tmp_disk_tables | 0     |
1 row in set (0.00 sec)

mysql> SHOW COLUMNS FROM mysql.user;
42 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Created_tmp_disk_tables';
| Variable_name           | Value |
| Created_tmp_disk_tables | 1     |
1 row in set (0.00 sec)

The field information returned with a query result set contains the same information as returned by SHOW COLUMNS, so a SELECT * FROM my_table LIMIT 0 should achieve the same thing without creating an on-disk temporary table per query.

A quick example to just grab the field names in PHP...

$mysql = new mysqli('localhost', 'root', '', 'my_database');
$field_names = array();
$result = $mysql->query("SELECT * FROM my_table LIMIT 0");
$fields = $result->fetch_fields();
foreach ($fields as $fields)
    $field_names[] = $field->name;

Retrieving field info in this way is a little more awkward to decode. You'll have to consult the description of the underlying MYSQL_FIELD structure to pull out the data types and flags, but it runs about 7 times faster on my system.


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.