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 (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:5.1.49 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D3 (Medium) / R3 (Medium) / E3 (Medium)

[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.