content_image/706644.jpg
•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. 

## 8.2.3 Optimizing INFORMATION_SCHEMA Queries 

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: 

•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. 

<img src='content_image/706644.jpg'>

## Note 

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” . 

These INFORMATION_SCHEMA  tables are implemented as views on data dictionary tables, so queries on them retrieve information from the data dictionary: 

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 

