MySQL Bugs: #38918: selecting from information_schema.columns is disproportionately slow

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

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)

Thank you for a problem report. Verified with 6.0.6.

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)

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.

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.

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

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?

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

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

Pushed into 6.0.8-alpha  (revid:sergey.glukhov@sun.com-20081009075029-25tb04gzoxjcvdbn) (version source revid:sergey.glukhov@sun.com-20081009080631-3p1o3zegpf17ivyc) (pib:5)

Pushed into 5.1.30  (revid:sergey.glukhov@sun.com-20081009075029-25tb04gzoxjcvdbn) (version source revid:kgeorge@mysql.com-20081010130753-obt82wv52av801ed) (pib:5)

The versions are actually 5.1.31, 6.0.9.

Noted in 5.1.31, 6.0.9 changelogs.

Performance of SELECT * retrievals from INFORMATION_SCHEMA.COLUMNS
was improved slightly.

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)

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)

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)