Bug #42248 Information schema has no indexes
Submitted: 21 Jan 2009 18:27 Modified: 21 Jan 2009 19:39
Reporter: vladimiro falacci Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Information schema Severity:S5 (Performance)
Version:6.0.0-alpha-community-nt-debug OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[21 Jan 2009 18:27] vladimiro falacci
Description:
Information schema tables (in my case TABLES and COLUMNS) have no indexes. 
I use the schema with other RDBMS (i.e. SQL server, Sybase ASE, etc.) to recognise if a column is nullable or not, has a default, privileges, etc. The same system with MYSQL is extremely slow due to the lacks of indexes.
Opening a schema, even if it has not so much tables (i.e. 200), is very slow too.
Some index would be a great improvement.

How to repeat:
Just do a loop to retrieve IS_NULLABLE column from COLUMNS table in a server with 10/20 schemas with 200 tables each

Suggested fix:
Create indexes
[21 Jan 2009 18:59] Valeriy Kravchuk
Thank you for a problem report. What exact version, 6.0.x, do you use?

I think this is may be a duplciate of http://bugs.mysql.com/bug.php?id=19588. Please, check.
[21 Jan 2009 19:28] vladimiro falacci
MYSQL version is 6.0.0-alpha-community-nt-debug

this is a duplicate of bug found at http://bugs.mysql.com/bug.php?id=19588

It's not a real bug; it's just an optimization.
i think it would take at least 1 hour to fix it.
as the end user can't change the information_schema structure, only developers' team can do it. 
As you can see from the previous bug, there's a lot of people kepp fighting with it.

B.R.
[21 Jan 2009 19:39] Valeriy Kravchuk
OK, so let's consider this a duplicate of http://bugs.mysql.com/bug.php?id=19588.