Bug #15883 INFORMATION_SCHEMA considers all databases
Submitted: 20 Dec 2005 9:59 Modified: 10 May 2006 12:38
Reporter: Hakan Küçükyılmaz Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.0/5.1 OS:Any (All)
Assigned to: Sergey Glukhov CPU Architecture:Any

[20 Dec 2005 9:59] Hakan Küçükyılmaz
Description:
Given several fairly big databases an INFORMATION_SCHEMA operation takes longer then necessary.

How to repeat:
Create two or more big databases (> 10.000 tables) and run a statement like:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_SCHEMA = 'DB1' AND TABLE_TYPE = 'BASE TABLE'

The more databases there are, the longer the statement above takes.

Suggested fix:
WHERE clause should be considered. Only the given databases should be used for searching.
[10 May 2006 12:38] Miguel Solorzano
Marked duplicate of bug: http://bugs.mysql.com/bug.php?id=19588
since are related and the last one has a C application for to create
the test case.
[3 Sep 2010 1:21] Andrew Dalgleish
This was fixed in 5.1.21

http://dev.mysql.com/doc/refman/5.1/en/information-schema-optimization.html