Bug #19588 INFORMATION_SCHEMA performs much too slow on large servers
Submitted: 6 May 2006 23:47 Modified: 12 Dec 2007 3:25
Reporter: Markus Popp Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Information schema Severity:S5 (Performance)
Version:5.0, 5.1 OS:Any (all)
Assigned to: Sergey Glukhov CPU Architecture:Any
Tags: affects_connectors, bfsm_2007_02_01, Q1
Triage: D2 (Serious)

[6 May 2006 23:47] Markus Popp
This is a known issue and has been discussed during the MySQL Users Conference - however, there should be an official bug report for it and the severity should definitely be S1!

The low performance of information_schema has several bad effects on servers that host high amounts of data - many of them, I've discovered through db4free.net. I wasn't able to update to phpMyAdmin (databases could not be displayed because of timeouts) and found out that it resulted from changing to retrieve objects from information_schema instead of SHOW commands in phpMyAdmin. Many client applications make use of information_schema and they all suffer from this problem - one for example is MySQL Front.

The latest issue is that I can't even run "SELECT * FROM INFORMATION_SCHEMA.TABLES" on db4free's MySQL 5.0 server (ca. 5,500 databases, estimated 90,000 tables) anymore (Lost connection to MySQL server during query).

Also refer to http://db4free.blogspot.com/2006/04/downside-of-informationschema.html and the comments of this blog article and also to http://sourceforge.net/tracker/index.php?func=detail&aid=1466527&group_id=23067&atid=37740..., the bug report that I initially filed for phpMyAdmin. AFAIK, Robin Schumacher has been informed about this issue.

How to repeat:
Run queries from information_schema or use client applications like phpMyAdmin > 2.8.0 or MySQL Front on database servers with high amounts of data.

Output from db4free.net:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 495 to server version: 5.0.21-max-log

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

mysql> use information_schema
Database changed
mysql> select count(*) from tables;
ERROR 2013 (HY000): Lost connection to MySQL server during query
[7 May 2006 18:47] [ name withheld ]
I agree. it should be a bug with S1 and not a known problem.
Very annoying
[8 May 2006 22:30] Godofredo Miguel Solorzano
Thank you for the bug report. I created 5500 databases and 15 tables for
each databases (see C application at the bottom) starting the servers
with their defaults values. I was unable to get the message error:
RROR 2013 (HY000): Lost connection to MySQL server during query;
However I got enough information regarding the performance:

miguel@hegel:~/dbs/5.1> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.10-beta-debug

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

mysql> use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from tables;
| count(*) |
|    88061 | 
1 row in set (2 hours 2 min 45.43 sec)


Verified on Windows and Linux.

#include <my_global.h>
#include <m_string.h>
#include "mysql.h"

#define DB_HOST			"hegel" //"localhost"
#define DB_USER			"miguel" //"root"
#define DB_PASSW		""
#define DB_NAME			"test"
#define DB_PORT			3306
#define DB_UNIX_SOCKET	"/tmp/mysql.sock" //NULL

void main( void )
  MYSQL mysql;
  int x, y;
	char my_sub[255],my_query[255];
  if (!mysql_real_connect(&mysql,DB_HOST,DB_USER,DB_PASSW,DB_NAME,
    printf("Error: %s\n",mysql_error(&mysql));
	printf("Connected to the server: %s\n",mysql_get_server_info(&mysql));

  for (y = 0; y <= 5500; y++)
	  int10_to_str((int) y,strmov(my_sub,
          "Create Database if not exists db"), 10);
    if (mysql_query( &mysql, my_sub) )
      printf("Error (query): %s\n", mysql_error( &mysql ));
      mysql_close( &mysql );
    int10_to_str((int) y,strmov(my_sub,
          "Use db"), 10);
    if (mysql_query( &mysql, my_sub) )
      printf("Error (query): %s\n", mysql_error( &mysql ));
      mysql_close( &mysql );
    for (x = 0; x <= 15; x++)
	    int10_to_str((int) x,strmov(my_sub,
                  "Create Table if not exists tb"), 10);

		   " (id int not null auto_increment primary key, name varchar(200))engine=MyISAM",NullS);
	 	  if (mysql_query( &mysql, my_query) )
        printf("Error (query): %s\n", mysql_error( &mysql ));
        mysql_close( &mysql );
[10 May 2006 12:39] Godofredo Miguel Solorzano
Bug: http://bugs.mysql.com/bug.php?id=15883 was marked as duplicate
of this one.
[10 May 2006 13:04] Paul Dubois
The databases don't even need to be "large" before the slowness
of INFORMATION_SCHEMA retrievals become noticeable.  For a
database with just a couple of hundred tables, I observe response
times of several seconds for a query like this:

| TABLE_NAME        |
203 rows in set (3.45 sec)

The equivalent SHOW TABLES FROM db_name is virtually

The problem is not that the server has lots of tables in other databases.
There are fewer than 400 tables total:

| COUNT(*) |
|      390 | 
1 row in set (9.03 sec)
[15 May 2006 12:35] Sebastian Mendel
i must agree, this should really S1
[21 May 2006 12:35] Tordjman Yohan
I have the same problem.

On a large server, this makes the server very slow.

With 10 requests like that
, the server do not response anymore to other queries & go to crash after a "deadlock" of > 20 minutes.

This is a real S1 bug.
There are a need to restrict that to an admin for example....
[20 Jul 2006 7:43] David Hammink
SHOW TABLE STATUS ON  301 Table size Database costed the mysql server 51 seconds !!! (just in the mysql client

Indeed S1
Not acceptable 

Brought it down to  214 tables and the response was within 20 seconds (which is still slow but acceptable)

However the Query
SELECT * FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`='DatabaseName'

(the same database name) took up some 63 seconds
Something is not good here
[28 Aug 2006 14:05] Godofredo Miguel Solorzano
Bug: http://bugs.mysql.com/bug.php?id=21871 was marked duplicate of this one.
[31 Aug 2006 12:06] Marco Chiavacci
Also us have the same problem.
With phpmyadmin 2.8.x, that use some select in information_scheme tables, all became slow and with a lot of customer, mysql server crash.
I think we must give us the possibility to ihibits access to information_schema db via some privileges, or simply change the way information_schema harvest information in fs.
It is a serious bug not simply a S5.
A customer(not superuser and with only access to his db) can, via multiple select to information_schema, crash server and this is not so usefull in a large enviroment.
Please dedicate some time to this S1 bug.
[8 Sep 2006 19:02] Michael Rose
I agree, this should be an S1. Using 5.0.24a it took well over a minute to run SELECT TABLE_NAME FROM TABLES; and this is with a small number of databases and 2400 tables.
[4 Oct 2006 11:35] Manuel Mausz
I agree aswell. Select from information_schema took way too long even on small instances. Not to mention the cpu resources these selects need. 3 selects and our server is nearly off for about 30 minutes (on the bigger instances).

Running 5.0.24a
[17 Oct 2006 15:37] Warren Ashcroft
Is anybody actually looking into this? This is VERY serious!!
[17 Oct 2006 15:49] Timour Katchaounov
Dear Mr. Ashcroft,

As the bugs status says, the bug is "In Progress", this means that
the assigned developer is working on the bug. We are very well aware
that this is a serious problem and are doing our best to solve it.

[17 Oct 2006 16:00] Warren Ashcroft
With all due respect, this bug was created 5-6 months ago and STILL has only an S5 severity.

Maybe an update from MySQL/Developer on it?
[17 Oct 2006 16:43] Tordjman Yohan
before my duplicate (since 2months) nothing was done about it.

In 6 months noone was be able to add a directive like skip_show_database..to forbid the readdir....

New versions of phpmyadmin can DoS the server with less than 10 clients connected...
[19 Oct 2006 8:34] Marc Villemade
FYI, I have been told by the support guys that this problem will be fixed early November.

Hopefully, it will be. I'm getting really bad performance because of this and all my shared hosting customers are getting pissed... :(

Just thought i'd let you guys in the know...
[14 Nov 2006 16:35] Marco Chiavacci
Ther's some more news about?
It's mid november and nothing in 6 month was done about this SERIOUS and CRITICAL S1 bug.
Please give us an alternative if is a real big problem to fix it.
[15 Nov 2006 9:39] Sergey Glukhov
The preliminary fix is almost done.
The fix speeds up most of 'SELECT' requests
listed in bug report and some others.
[21 Nov 2006 7:25] 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:


ChangeSet@1.2302, 2006-11-21 11:42:23+04:00, gluh@mysql.com +11 -0
  Bug#19588 INFORMATION_SCHEMA performs much too slow on large servers
  optimization of 'WHERE' condition for I_S tables:
  extract base name & table name values from 'WHERE' when it's possible
  optimization of table opening:
  open .frm file only if we can obtain necessary values from .frm file
  skip table opening if we need base name & table name only(for I_S.TABLES table)
[21 Nov 2006 11:23] Tordjman Yohan
I don't see in the patch any remove of the "readdir".....
[30 Jan 2007 16:33] Matthias Kuklok
Hi, status is still 'In progress'. Are there any updates?
Is there any idea in which version and when the bug is fixed?
[8 Feb 2007 10:36] 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:


ChangeSet@1.2368, 2007-02-08 14:33:02+04:00, gluh@mysql.com +11 -0
  Bug#19588 INFORMATION_SCHEMA performs much too slow on large servers
  optimization of 'WHERE' condition for I_S tables:
  extract base name & table name values from 'WHERE' when it's possible
  optimization of table opening:
  open .frm file only if we can obtain necessary values from .frm file
  skip table opening if we need base name & table name only(for I_S.TABLES table)
[9 Mar 2007 10:28] Sergey Glukhov
This bug will be fixed when 'WL#3732 Information schema optimization'
will be implemented.
[3 Aug 2007 15:12] Alan Tam
I understand from the WL that many things need to be done to speed up access to I_S. But, why can't we cache it? I am sure a lot of people have status I_S over multiple days, if not months or even years.
[7 Aug 2007 9:09] Sergei Golubchik
It can be cached, of course, but it's not as easy as it may look. A cache, for example, must be invalidated on DDL statements. There's a more difficult problem - our users expect that if a table is copied into data directory, it becomes visible in SHOW statements and I_S tables. This makes caching a very tricky business.
[7 Aug 2007 9:57] Alan Tam
IMHO, no sane person will drop in some files _into_ a directory owned by a running DB instance and expect the files to be detected by the DB on-the-fly.
[7 Aug 2007 11:08] Sebastian Mendel
[7 Aug 11:57] Alan Tam

"IMHO, no sane person will drop in some files _into_ a directory owned by a running DB instance and expect the files to be detected by the DB on-the-fly."

Why ...
... should others expect the same as you?
... should someone think this should not work?
... should it not work if it could work?
... do you call people relying on this insane?
... does your mail client shows newly dropped mails in your inbox without restart?

cause it is a feature!
[7 Aug 2007 11:20] Alan Tam
I said it is insane to expect such a feature. No implication for fact that the DB really offers such a feature, or other people depending on it when the DB says it can. I simply have no intention to start a flame war.

Perhaps the original designer didn't realize (s)he needs to support fast INFORMATION_SCHEMA retrieval. Not until recently we have lots of Object-Relational mappers which requires access to the fields and keys of all tables during every build/run of an application.

Now we realize the importance of the bug, and maybe there is no quick solution. Can we simply drop the perhaps-unintended feature so that more users are happy?
[7 Aug 2007 11:21] Roland Bouman
Personally, I don't think it should be a problem to not have this 'feature' working for a information_schema cache. 

I mean, normally people expect to be able to create stored routines in databases - that is something that is currently not supported either for the information_schema. I guess my point is that it is acceptable to allow exceptions for these system databases.

As for being able to add a directory into the data directory and have the results show up - is that documented anywhere?
[7 Aug 2007 11:25] Tordjman Yohan
Since one year i asked for a configuration parameter to disable the use of INFORMATION_SCHEMA... 

On a public service, i really don't care about information_schema & actually it can be a DoS for me... 

So until MySQL AB understand that... wait & see (& pray ?) :-)
[7 Aug 2007 11:36] Sebastian Mendel
Tordjman: as Alan already said, many ORM (could or should) rely on I_S - and it would be a drawback not giving your customers this feature

disabling I_S would not help much ... IMHO ... better fix this speed/DoS issue ...
[7 Aug 2007 11:53] Tordjman Yohan
I provide one of the biggest free mutualised web hosting in France, and i say that  i really don't want any I_S, cache on I_S, stupid readdir over 60000 directories...

Explain that: "When a user X with rights to db Y, witch plays with I_S, does a readdir of the directory of ALL the DB of the server ?"

If one of them does that, the server is dead. 

So, i'm going to continue praying .... :-/
[7 Aug 2007 12:04] Sebastian Mendel
i know this problem, we got reports about this issue too (phpMyAdmin), but i would not prefer disabling I_S ... but i also do not understand why MySQL is taking this problem not more seriously and fix this 'DoS' exploit ...
[7 Aug 2007 12:15] Tordjman Yohan
Sebastian Mendel: Yes i can't upgrade my phpmyadmin without patch it to disable the use of I_S
"but i would not prefer disabling I_S ..." , you, i speak for my 2.5 millions accounts... 
This is the same thing of "skip/safe_show_database"
[7 Aug 2007 12:35] Sebastian Mendel
> patch it to disable the use of I_S

just hiding the I_S from being listed or scan all queries for I_S access?

hiding databases can be configured in phpMyAdmin ...

> "but i would not prefer disabling I_S ..." , you, i speak for my 2.5
> millions accounts... 

hiding on the UI and disabling in the server is not the same ...

of course, if i was you i would hide/disable I_S too - but just as workaround and would still prefer fixing this issue and not hide I_S ...

> This is the same thing of "skip/safe_show_database"

i don't think so, this was about showing databases without proper rights, or?
[7 Aug 2007 12:43] Tordjman Yohan
for phpmyadmin , the last time i did that, i simply impose that the DB version is prior the use of I_S.

I want to disable it, not hide...
hiding = can be accessible & DoS able...

for the skip/safe show db, i hope that mysql will not do like pgsql that everyone can see the name of all dbs...
[8 Aug 2007 9:04] Sergei Golubchik
Just to mention that although the patch http://lists.mysql.com/commits/19550 won't help in the case, described by Tordjman, we'll try to come with a solution for this too.
[24 Aug 2007 2:38] wretgt dsaadwesdf
Very annoying, I have ran my box for almost a half year with a torrent tracker that has phpmyadmin integrated, not to mention it have gone "away" somewhat too often, and made all other sites to suffer as well..
Somehow (lets call it LUCK) I spotted the I_S database and was wondering what does it do (and found this thread), otherwise I'd have been wasted more money into that machine with no results at all, as it happened before, and probably happens to many other guys all over.
Even worse that it's in the stable Debian tree, I just can't understand how does a bug like that make its way into stable releases..
[4 Oct 2007 23:10] Victor Reus
Hi Sergey,

How can I apply this path to the 5.0.45?

We have a productoin server that is hitting this issue.

Is there any posibility to apply this patch (19588) and how?

Thanks in advance.

[4 Oct 2007 23:36] Tordjman Yohan
I didn't see any *good* patch for that...

The only thing to do, is to pray... (1.5 years to correct this... awfull thing... for very small servers ;-) )
[5 Oct 2007 5:36] Victor Reus
Hi all

But Sergey have a patch, can this patch be applied to the source of 5.0.45?

Isn't that the case?

Thanks in advance.
[5 Oct 2007 6:16] Tordjman Yohan
=> Target Version:   5.1.23
& i do not see anything that bypass (or comment) the ugly readdir...
[5 Oct 2007 10:47] Sergei Golubchik
Tordjman: if you looking at the line with readdir() and is waiting for a patch that removes that line - I'm sorry, it won't happen anytime soon. The feature is called in our todo "a super-database", it is being considered, we understand that it is necessary, still it's not planned for 5.x (or even 6.x) releases.

What this patch does (and there will be more patches like this) - it significantly reduces the number of times when the server gets to readdir() call, the optimization works on the upper level and you won't see it by looking at readdir() line alone. Nevertheless, certain queries work tens or hundreds of times faster with this patch.

But, frankly speaking, the first patch doesn't help much in your case :(
(but it's not the last yet)

Victor: it should be possible. I doubt it'll apply automatically, must the code didn't change much since 5.0, so it should be doable. This is the final changeset with the patch, if you need it:
[5 Oct 2007 10:56] Tordjman Yohan
I understant but, for me, only one "readdir" will kill all the db...

i have something like 60 000 db on it...and... how much tables? :-)

(could i do something like a return faillure instead of the readdir ? )
[6 Nov 2007 12:36] Sergey Glukhov
pushed addon for WL#3732 into 6.0
[5 Dec 2007 13:40] Sergei Golubchik
pushed in 5.1.21
[9 Dec 2007 22:00] Jared S
Excellent improvement noticed in 5.1.22
[11 Dec 2007 4:49] Jared S
I am Genuinly confused, although 5.1.22 worked few days ago, I am now seeing 5.0.52 as faster server for I_S querys.

What would cause 5.1 to go back to its old ways?

SELECT             information_schema.columns.column_name,
FROM               information_schema.columns
LEFT OUTER JOIN  information_schema.key_column_usage ON
information_schema.key_column_usage.table_schema = information_schema.columns.table_schema
AND information_schema.key_column_usage.table_name = information_schema.columns.table_name
AND information_schema.key_column_usage.column_name =
[12 Dec 2007 3:25] Paul Dubois
Noted in 5.1.23, 6.0.4 changelogs.

INFORMATION_SCHEMA implementation changes were made that optimize
certain types of queries for INFORMATION_SCHEMA tables so that they
execute more quickly. 
provides guidelines on how to take advantage of these optimizations
by writing queries that minimize the need for the server to access
the filesystem to obtain the information contained in
INFORMATION_SCHEMA tables. By writing queries that enable the server
to avoid directory scans or opening table files, you will obtain
better performance.
[12 Dec 2007 4:52] Jared S
5.0\5.1 Benchmark, shows heavy IO.

Attachment: I_S Benchmark.jpg (image/pjpeg, text), 169.33 KiB.

[12 Dec 2007 8:18] Timour Katchaounov
Hi, could you please provide some numbers:
- execution time in both versions, and
- cardinality of the I_S tables involved in the query
[12 Dec 2007 9:21] Jared S
1000 MS+ on *P4*
1000 MS- on C2
< 900 results on 5.1
< 800 results on 5.0

I think the improvement I notice in 5.1 was some testing I did on my home computer which is very fast.

I strongly suggest that this bug goes to QA, where a dedicated machine with stagment databases can be used to determine if this hotfix has made the problem worse or better and by how much.

A P4 or lower should be used in testing.
[23 Feb 2008 21:05] Szilveszter Molnar
Hi, i have a question: while it is really wonderfull that this bug is being addressed in the 5.1 version ... will this be solved for the 5.0 ? 

thank you,
[4 Sep 2008 18:55] Christian Rishoj
Running MySQL 5.1.26-rc it still takes 7+ seconds to return the 73 foreign key constraints defined in a database with 89 tables. 

I am using the following query:

SELECT constraint_name, table_name, column_name, referenced_table_name, referenced_column_name
 FROM information_schema.key_column_usage
 WHERE table_schema = SCHEMA()
 AND referenced_table_schema = table_schema
 ORDER BY constraint_name, ordinal_position;

I have tried replacing SCHEMA() and table_schema with constants (strings) – it does not affect performance.

Is there any way to improve the performance?
[5 Sep 2008 10:07] Sergey Glukhov
you can use following queries, they should work faster:

SELECT constraint_name, table_name, column_name, referenced_table_name,
 FROM information_schema.key_column_usage
 WHERE table_schema = SCHEMA()
 AND table_schema = referenced_table_schema   // change field places
 ORDER BY constraint_name, ordinal_position;


SELECT constraint_name, table_name, column_name, referenced_table_name,
 FROM information_schema.key_column_usage
 WHERE table_schema = SCHEMA()
 AND referenced_table_schema = SCHEMA()
 ORDER BY constraint_name, ordinal_position;

see also
[5 Sep 2008 12:12] Christian Rishoj
The latter executes about a second faster than the original. Still, it seems amazingly slow (7+ seconds).
[21 Jan 2009 19:44] Valeriy Kravchuk
Bug #42248 was marked as a duplicate of this one (on 6.0.x).
[21 Jul 2009 7:16] Sander Pilon
Still there using 5.1.36.


Takes hours to complete :(
[17 Feb 2010 1:16] Itamar Reis Peixoto
I have the same problem, it's possible to fix this bug ?
[4 Mar 2010 18:40] Xueshan Feng
We just upgraded to 5.1.43 from 5.0. This problem persists. An example from mysqldumpslow against msyql-slow log:

Count: 31  Time=166.34s (5156s)  Lock=0.00s (0s)  Rows=0.0 (0), 8users@2hosts

This query does have constant condition, but it is still very slow and blocking other applications to use the service.

If no short term solution, any work-around?

[26 Mar 2010 1:40] Warren Ashcroft
This was never really fixed and needs to be looked at again... Still extremely slow in 5.1.45!

The worst thing is that INFORMATION_SCHEMA is used in all sorts of places, such as the MySQL GUI Tools so certain things take far too long to run (that is if they even get a chance to complete).
[26 Mar 2010 1:44] Warren Ashcroft
...since this bug is now marked as closed, does that mean folks at MySQL ignore new comments on it? If so, we need to open a new bug!
[8 Jun 2010 13:14] Glenn Plas
Mysql 5.1.45 32bit Here:

This -> select TRIGGER_NAME from information_schema.TRIGGERS where TRIGGER_NAME='tr_LastSeen_debr'\G

Took me more than 411 seconds and got killed with 'lost connection' 

I also tried this on 5.1.47-MariaDB x86_64 (on a slave with nothing to do) and same there, the query actually never came back.  Always hanging on 'Opening tables' which I see elsewhere all the time, locking all my tables for an unknown reason.

It's so incredible that I see 2006 as first report then 4 years later I try selecting from the information scheme and nothing changed really...

I was hoping for MariaDB to help me out but unfortunately that didn't help either.   What an illusion this all is.
[9 Jun 2010 3:49] Amr Hamdy
I totally agree with Glenn ..
I think the situation is getting more complicated now. I don't know if Oracle would ever wish to help solving such serious problems.
[3 Sep 2010 1:03] Roel Van de Paar
See bug #56178
[6 Oct 2010 15:40] Dave Rix
I have the same problem.
My system has 700 databases, each with 100+ tables, and doing anything with the information_schema is just painful!
[28 Oct 2010 14:20] Tomasz Drobiszewski
Why is this bug closed ? It's not work at all. SQL:2003 works good only for simple databases.
[11 Mar 2011 10:11] Michal Ziemniakowski
this bug was never really fixed, these queries take hours on 5.1.54, please reopen the bug and fix it!
[30 Aug 2011 18:26] Todd Schnack
Please PLEASE open this bug back up and fix it!
[30 Aug 2011 18:42] Tordjman Yohan
i made a patch for mine,

in mysql-5.0.92/sql/sql_show.cc

return 0 instead of return (find_files(... (2 times)
and return 0 instead of return files->push_back(thd->strdup(INFORMATION_SCHEMA_NAME.str));

when a request is done to INFORMATION_SCHEMA it does an error... (not a pb for me)