Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Aug 11 '12 at 1:00

This question came from our site for professional and enthusiast programmers.

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 '12 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 '12 at 18:41
    
Try querying the information_schema tables directly, but indeed, knowing why that's a problem at all might be interesting to give a more relevant answer. –  Mat Aug 8 '12 at 19:04
    
@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 '12 at 20:59
1  
@Mat: Not a bad idea. Voted to migrate to dba. –  Mark Aug 9 '12 at 16:11

3 Answers 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.

share|improve this answer
    
Thnx for adding another permtuation to my collection :) –  ypercube Aug 11 '12 at 22:47

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

share|improve this answer

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
share|improve this answer
    
I should have mentioned that I'm actually using MyISAM. Tried setting this anyway, but it didn't yield any benefit. –  Mark Aug 11 '12 at 1:32
    
Have you considered ALTER TABLE foo ENGINE=InnoDB? :) Is there a good reason for using MyISAM? –  Aaron Brown Aug 11 '12 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. –  Mark Aug 11 '12 at 5:25
    
@AaronBrown +1 for this answer because whoever faces this situation with an all-InnoDB database needs this info. –  RolandoMySQLDBA Aug 11 '12 at 20:42
    
+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. –  Buttle Butkus Jul 23 '13 at 21:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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