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
• Status: Duplicate
• 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

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