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
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
