Bug #38918 selecting from information_schema.columns is disproportionately slow
Submitted: 20 Aug 2008 15:44 Modified: 13 Nov 2008 3:29
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.1.26,6.0.6 OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any
Tags: regression

[20 Aug 2008 15:44] Philip Stoev
Description:
Selecting from INFORMATION_SCHEMA.COLUMNS is very slow, even when compared with the rest of INFORMATION_SCHEMA.

How to repeat:
Have a database with 78 tables, 10 columns per table.

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

mysql> select count(*) from information_schema.tables;
+----------+
| count(*) |
+----------+
|       78 |
+----------+
1 row in set (0.00 sec)
[20 Aug 2008 18:33] Valeriy Kravchuk
Thank you for a problem report. Verified with 6.0.6.
[20 Aug 2008 18:36] Valeriy Kravchuk
Impact of this bug in 5.1.26 is much more serious:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.26-rc-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

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

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

mysql> select count(*) from information_schema.tables;
+----------+
| count(*) |
+----------+
|     3085 |
+----------+
1 row in set (0.09 sec)
[20 Aug 2008 18:40] Valeriy Kravchuk
I was not able to repeat this kind of a disproportion in 5.0.66a, so this is a regression of a kind, it seems.
[25 Aug 2008 9:28] Sergei Glukhov
The difference between 'select from tables' and 'select from coulmns' is that
we need to open FRM file for 'columns' table to get fields count. For tables
we do not need it, we just count table names.
you can find additional information how it works here:
http://dev.mysql.com/doc/refman/5.1/en/information-schema-optimization.html
So select count(*) from tables is always faster.
 
However explain returns wrong result for 'select count(*) from columns'.
It returns 'skip_open_table' but it should be 'open_frm_only'.
The fix is below.
[25 Aug 2008 9:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/52424

2719 Sergey Glukhov	2008-08-25
      Bug#38918 selecting from information_schema.columns is disproportionately slow
      The problem: table_open_method is not calculated properly if '*' is used in 'select'
      The fix: added table_open_method calculation for such case
[25 Aug 2008 9:53] Philip Stoev
Unfortunately, even if there is a valid reason for this being slow, this does not make it any faster :-) Also, if all FRM files are open, does this trash the table cache?
[5 Sep 2008 11:36] Georgi Kodinov
Let me start by the fact that the glass is not half-empty : it's half full.
This is not a result of a regression, but rather of a partial improvement.
The fix for bug #19588 introduced a speedup in handling INFORMATION_SCHEMA.tables.
But it speeds up mostly INFORMATION_SCHEMA.tables.
That's why there's a disproportion : the SELECT from INFORMATION_SCHEMA.tables became faster, not that the SELECT from INFORMATION_SCHEMA.columns became slower.
Unfortunately the root cause of this slowness (the way the MySQL server handles table metadata in separate files instead doing this in real database tables) is not trivial to fix and it requires a fair amount of effort. There are plans to fix the root problem, but this is work in progress and I don't have any estimates yet when this will be ready.
The fix here fixes a small problem that will make this particular query run a bit faster, but this will not be the speedup that INFORMATION_SCHEMA.tables queries have got by the fix for bug #19588
[9 Oct 2008 7:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/55874

2770 Sergey Glukhov	2008-10-09
      Bug#38918 selecting from information_schema.columns is disproportionately slow
      The problem: table_open_method is not calculated properly if '*' is used in 'select'
      The fix: added table_open_method calculation for such case
[10 Nov 2008 10:50] Bugs System
Pushed into 6.0.8-alpha  (revid:sergey.glukhov@sun.com-20081009075029-25tb04gzoxjcvdbn) (version source revid:sergey.glukhov@sun.com-20081009080631-3p1o3zegpf17ivyc) (pib:5)
[10 Nov 2008 11:36] Bugs System
Pushed into 5.1.30  (revid:sergey.glukhov@sun.com-20081009075029-25tb04gzoxjcvdbn) (version source revid:kgeorge@mysql.com-20081010130753-obt82wv52av801ed) (pib:5)
[11 Nov 2008 16:06] Paul DuBois
The versions are actually 5.1.31, 6.0.9.
[13 Nov 2008 3:29] Paul DuBois
Noted in 5.1.31, 6.0.9 changelogs.

Performance of SELECT * retrievals from INFORMATION_SCHEMA.COLUMNS
was improved slightly.
[19 Jan 2009 11:29] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:07] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:13] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)