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:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.0 OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any

[18 Apr 2007 22:37] Adam Dixon
Description:
Reported as suggested by Reggie.

Sub optimal I_S handling when dealing with many tables.

070326 15:33:37 7 Connect root@192.168.147.152 on db1
7 Query SHOW VARIABLES
7 Query SHOW COLLATION
7 Query SET character_set_results=NULL
7 Init DB db1
070326 15:33:38 7 Query SELECT * FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE != 'VIEW' AND
TABLE_CATALOG='db1'

070326 15:34:08 8 Connect     root@192.168.147.152 on db1
8 Query       SHOW VARIABLES
8 Query       SHOW COLLATION
8 Query       SET character_set_results=NULL
8 Init DB     db1
8 Query       KILL QUERY 7

How to repeat:
Database with approximately 2500 databases totaling 60k tables.
[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