Optimizing INFORMATION_SCHEMA Queries •The optimization cannot be used if the derived table contains  UNION . •The derived table cannot use a  LIMIT  clause. •Conditions containing subqueries cannot be pushed down. •The optimization cannot be used if the derived table is an inner table of an outer join. •If a materialized derived table is a common table expression, conditions are not pushed down to it if it is referenced multiple times. •Conditions using parameters can be pushed down if the condition is of the form  derived_column  > ? . If a derived column in an outer  WHERE  condition is an expression having a  ?  in the underlying derived table, this condition cannot be pushed down. •Try to query only  INFORMATION_SCHEMA  tables that are views on data dictionary tables. •Try to query only for static metadata. Selecting columns or using retrieval conditions for dynamic metadata along with static metadata adds overhead to process the dynamic metadata. 1708 These  INFORMATION_SCHEMA  tables are implemented as views on data dictionary tables, so queries on them retrieve information from the data dictionary: Comparison behavior for database and table names in  INFORMATION_SCHEMA queries might differ from what you expect. For details, see Section 10.8.7, “Using Collation in INFORMATION_SCHEMA Searches” . Applications that monitor databases may make frequent use of  INFORMATION_SCHEMA  tables. To write queries for these tables most efficiently, use the following general guidelines: 8.2.3 Optimizing INFORMATION_SCHEMA Queries Note CHARACTER_SETS CHECK_CONSTRAINTS COLLATIONS COLLATION_CHARACTER_SET_APPLICABILITY COLUMNS EVENTS FILES INNODB_COLUMNS INNODB_DATAFILES INNODB_FIELDS INNODB_FOREIGN INNODB_FOREIGN_COLS INNODB_INDEXES INNODB_TABLES INNODB_TABLESPACES INNODB_TABLESPACES_BRIEF INNODB_TABLESTATS KEY_COLUMN_USAGE PARAMETERS PARTITIONS REFERENTIAL_CONSTRAINTS RESOURCE_GROUPS ROUTINES SCHEMATA STATISTICS TABLES TABLE_CONSTRAINTS TRIGGERS VIEWS