We have a MySql external data source and our DBA is reporting that this query takes nearly a minute and is causing performance issues on the server, which hosts the back-end for several applications.
SELECT * FROM (SELECT TABLE_SCHEMA CAT, NULL SCH, TABLE_NAME NME from information_schema.tables WHERE TABLE_TYPE NOT IN (‘INDEX’,‘SEQUENCE’,‘SYSTEM INDEX’,‘SYSTEM TABLE’,‘SYSTEM TOAST INDEX’,‘SYSTEM TOAST TABLE’,‘SYSTEM VIEW’,‘TEMPORARY TABLE’,‘TEMPORARY VIEW’,‘TYPE’)) t WHERE UPPER(CAT) NOT IN (‘INFORMATION_SCHEMA’,‘SYS’);
In addition, the DBAs have implemented a job which kills queries that take longer than 10 seconds, and this query nearly always gets killed before completion. As a result, this MySql data source is nearly unusable because we can never get the metadata refreshed.
Any suggestions?