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 lar...@soe.ucsc.edu
UCSC Genome Bioinformatics Group http://genome.ucsc.edu
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
