Optimize table lookup

[pivnicek]

re: https://keboola.slack.com/archives/C035LGNK5/p1540223823000100

https://dev.mysql.com/doc/refman/5.7/en/information-schema-optimization.html

[Halama]

It is caused by columns which requires OPEN_FULL_TABLE

If I fetch, it took few seconds:

SELECT 
 TABLE_NAME,
 TABLE_SCHEMA,
 TABLE_TYPE,
 TABLE_COMMENT
FROM INFORMATION_SCHEMA.TABLES as c WHERE c.TABLE_SCHEMA = 'dbname';

If I add TABLE_ROWS it takes about 4 minutes.

SELECT 
 TABLE_NAME,
 TABLE_SCHEMA,
 TABLE_TYPE,
 TABLE_COMMENT,
TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES as c WHERE c.TABLE_SCHEMA = 'dbname';

There is about 700 tables in database mixture of MyISAM and Innodb.

[Halama]

for reference https://keboola.zendesk.com/agent/tickets/9340