List:General Discussion« Previous MessageNext Message »
From:Laurence Meyer Date:August 16 2009 6:09am
Subject:show tables is slow/uncached
View as plain text  
We have a production database that has 4,000+ tables. For a variety of reasons, our CGIs
always
have to run "show tables" to generate a list of all tables in the database.

This is by far our slowest query; it currently takes about 100 milliseconds. It is
apparently never cached; here's a relevant transcript:

      mysql ...
      use hg18
      show tables
      4421 rows in set (0.09 sec)
      show tables
      4421 rows in set (0.13 sec)         <-- note about same speed (i.e. not cached)

I have seen this in versions 4.0.27-standard-log and 5.0.41-community

As an alternative, I tried the new SELECT ... FROM INFORMATION_SCHEMA.TABLES,
but it is much slower:

   SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'hg18';
   4421 rows in set (1.73 sec)
   SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'hg18';
   4421 rows in set (1.67 sec) <-- second run; not cached and still very slow

Here's an unindexed query in a large table to show that caching is working for normal
queries:

   flush table gc5Base;
   select count(*) from gc5Base;
   562977 rows
   select count(*) from gc5Base where sumSquares = 1684400;
   1 row in set (0.38 sec)
   select count(*) from gc5Base where sumSquares = 1684400;
   1 row in set (0.00 sec)

This is an almost exclusively read-only database (there shouldn't be any locking issues).
All the tables are MyISAM.

Does anyone know why "show tables" is so slow?

Is there any way to speed this up?

We can create our own table with a cached copy of the results of "show tables",
but we would prefer to avoid that because it is kludgy.

thanks!

-larry

Larry Meyer                                  larrym@stripped
UCSC Genome Bioinformatics Group          http://genome.ucsc.edu
Thread
show tables is slow/uncachedLaurence Meyer16 Aug
  • Re: show tables is slow/uncachedGlyn Astill16 Aug
  • Re: show tables is slow/uncachedDan Nelson17 Aug