Bug #27940 | connector and I_S suboptimal with many tables | ||
---|---|---|---|
Submitted: | 18 Apr 2007 22:37 | Modified: | 4 May 2007 7:57 |
Reporter: | Adam Dixon | Email Updates: | |
Status: | Duplicate | Impact on me: | |
Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
Version: | 5.0 | OS: | Any |
Assigned to: | Assigned Account | CPU Architecture: | Any |
[18 Apr 2007 22:37]
Adam Dixon
[1 May 2007 22:15]
Adam Dixon
echo "create database lots_tables;" | mysql -u root for ((i=0; i<=60000;i+=1)); do echo "create table table${i} (id int(11) not null, primary key (id));" | mysql -u root lots_tables; echo "Table $i created..."; done mysql> select count(1) from TABLES; +----------+ | count(1) | +----------+ | 31370 | +----------+ 1 row in set (13.90 sec) 60,000 tables I would say could take > 30 seconds. mysql> explain SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE != 'VIEW' AND TABLE_CATALOG='lots_tables'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: TABLES type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 Extra: Using where 1 row in set (0.00 sec)
[2 May 2007 7:26]
Valeriy Kravchuk
Verified based on a test case from the last comment.
[4 May 2007 7:57]
Sergei Glukhov
duplicated with bug#19588 INFORMATION_SCHEMA performs much too slow on large servers