MySQL Bugs: #50701: WB exhausts system memory for large schema with 1000s of tables.

Bug #50701	WB exhausts system memory for large schema with 1000s of tables.
Submitted:	28 Jan 2010 20:34	Modified:	12 May 2010 12:15
Reporter:	J Jorgenson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.14	OS:	Any
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any
Tags:	memory limits schema

Description:
My desktop system, Win XP pro with 2G ram, is nearly crashed when opening the  SQL Editor, because the 'Physical Schema' is always queried for the active database.  

I've got a single database schema with 10,000s of tables (normalization is not the issue, the data is disperate).  We use an automated system for creating/dropping tables and the quantity of tables is extremely large.

Additionally because of the excessively large schema, queries on the database can hang, when max_open_tables value is reached because the info-schema requires to open each table to full fill the WB schema query: 
  SELECT 'table' AS 'OBJECT_TYPE', TABLE_CATALOG as 'CATALOG', TABLE_SCHEMA as
   'SCHEMA', TABLE_NAME as 'NAME' FROM information_schema.tables WHERE 
  table_type<>'VIEW' AND table_schema = 'myschema'

How to repeat:
Create a schema with 10,000s of tables and let the WB do its automatic query of the schema.

Suggested fix:
A) Give user ability to DISABLE query of schema through the options/preferences.

B) Give user ability to NOT auto-query of schema through the options/preferences.

C) Give user ability to STOP the schema query for large schemas.

D) Limit the number of tables returned from the schema query to 1 or 2K.
   How would they view tables beyond the limit, if they purposely want to work with an large schema?

we'd ad disabling of overview/auto-querying in profile (or in preferences)

There should be 2 new options:
- Disable Live Schema Overview
- Show Schema Names Only in Catalog List

Added 2 options mentioned by Alfredo. Works on all platforms.

confirmed in repository

An entry has been added to the 5.2.21 changelog:

When the SQL Editor was launched, MySQL Workbench became overloaded, due to a large number of tables (10,000+) in the schema.

MySQL Workbench has been changed to include two new options in the Workbench Preferences dialog: Disable Live Schema Overview and Show Schema Names Only in Catalog List. This allows the application to be configured to handle schemata with large numbers of tables.