I have a server registration to MySQL and other databases. When I execute queries in the Query Window for MySQL they are fast. But If I try to Create or Alter a table is to can 30 seconds for the dialog to come up. But Create and Alter of tables on other databases are fast. Why is it slow for MySQL?
Response
Niels Gron over 11 years ago
Aqua Data Studio uses the MySQL information_schema database to get meta data such as the list of databases, tables and other objects. When Creating or Altering a table, Aqua Data Studio will query for a list of databases to provide a list to the user in the GUI. The MySQL information_schema database is known to have performance problems. These tables don’t have indexes and you can’t manually add any. The queries return quickly if you don’t have many rows in the information_schema.tables. You can test the performance of your database by issuing queries to the information_schema.tables or other tables in the information_schema database to see the response time. If you do have performance problems with the information_schema database, then you may experience slow GUI behavior in Aqua Data Studio since it needs to query these tables.
You can read the MySQL bug report filed in May 2006, with the comments for 5 years in the link below. The bug was closed and everyone is wondering why.
http://bugs.mysql.com/bug.php?id=19588