List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:August 17 2009 1:31am
Subject:Re: show tables is slow/uncached
View as plain text  
In the last episode (Aug 15), Laurence Meyer said:
> 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)

"SHOW TABLES" can't be cached; it has to check for tables that have been
added since the previous query (possibly by someone copying .MYI files into
the databse directory).  If you set the open_files_limit and
table_open_cache variables large enough that mysqld can keep all the tables
open at the same time, it might run faster, but I'm not sure that "SHOW
TABLES" even uses the table cache.  It looks like it does a directory scan. 
Querying INFORMATION_SCHEMA is even slower since it has to generate a
virtual table containing all tables in all databases, then filter it on your
WHERE clause.

In your case, caching the results in your own table might be the best
solution.
 
-- 
	Dan Nelson
	dnelson@stripped
Thread
show tables is slow/uncachedLaurence Meyer16 Aug
  • Re: show tables is slow/uncachedGlyn Astill16 Aug
  • Re: show tables is slow/uncachedDan Nelson17 Aug