Bug #56178 SELECT on INFORMATION_SCHEMA: do not scan/open all files

• Submitted: 23 Aug 2010 0:59
• Modified: 24 Dec 2012 10:02
• Reporter: Roel Van de Paar
• Status: Duplicate
• Category: MySQL Server: Information schema
• Severity: S2 (Serious)
• Version: 5.1.49
• OS: Any
• CPU Architecture: Any

[23 Aug 2010 0:59] Roel Van de Paar

Description:
mysql> select count(*) from triggers;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (39.71 sec)

How to repeat:
use information_schema;
select count(*) from triggers;

[23 Aug 2010 1:09] Roel Van de Paar

(This also makes WorkBench very slow for certain actions)

[23 Aug 2010 1:40] Roel Van de Paar

Looks like it connected with how many tables there are on a system. select count(*) from triggers takes 0.1s on a colleague's system with only one table/one trigger. See bug #38918. Results on my system:

=======
mysql> select count(*) from information_schema.columns;
+----------+
| count(*) |
+----------+
|   137379 |
+----------+
1 row in set (13.92 sec)

mysql> select count(*) from information_schema.tables;
+----------+
| count(*) |
+----------+
|    20185 |
+----------+
1 row in set (0.85 sec)

mysql> select count(*) from information_schema.columns;
+----------+
| count(*) |
+----------+
|   137379 |
+----------+
1 row in set (13.70 sec)

mysql> select count(*) from information_schema.tables;
+----------+
| count(*) |
+----------+
|    20185 |
+----------+
1 row in set (0.24 sec)
=======

Even if it is slower because of many tables and the bug above, why does a check for columns (seems more intense than triggers) complete much faster than a check for triggers?

[23 Aug 2010 1:58] Roel Van de Paar

Issue repeatable even with 0 triggers.

[3 Sep 2010 7:22] Roland Bouman

As long as the metadata facility is based on .frm files, it is likely a lot faster if MySQL would only scan the data dir for .TRN and TRG files to answer queries about triggers, and then (when found) cross check to see if the associated table exists.

[25 Oct 2010 23:44] Roel Van de Paar

mysql> select * from REFERENTIAL_CONSTRAINTS;
[... output ...]
66 rows in set (5 min 56.65 sec)

Seen today on zero-load, medium-tuned, x64, 5.1.49.

[26 Oct 2010 0:05] Roel Van de Paar

Please, review bug #19588

[24 Dec 2012 10:02] Erlend Dahl

Fixed as a duplicate of an internally filed bug:

[15 Aug 2011 17:37] Paul Dubois (PDUBOIS)

Noted in 5.5.16, 5.6.3 changelogs.

The metadata locking subsystem added too much overhead for
INFORMATION_SCHEMA queries that were processed by opening only .frm
or .TRG files and had to scan many tables. For example, SELECT
COUNT(*) FROM INFORMATION_SCHEMA.TRIGGERS was affected.