<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="data-dictionary-information-schema">
      </a>
      16.5 INFORMATION_SCHEMA and Data Dictionary Integration
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045167705200">
  </a>
  <p>
   With the introduction of the data dictionary, the following
   <a class="link" href="information-schema.html" title="Chapter 28 INFORMATION_SCHEMA Tables">
    <code class="literal">
     INFORMATION_SCHEMA
    </code>
   </a>
   tables are
      implemented as views on data dictionary tables:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-character-sets-table.html" title="28.3.4 The INFORMATION_SCHEMA CHARACTER_SETS Table">
       <code class="literal">
        CHARACTER_SETS
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-check-constraints-table.html" title="28.3.5 The INFORMATION_SCHEMA CHECK_CONSTRAINTS Table">
       <code class="literal">
        CHECK_CONSTRAINTS
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-collations-table.html" title="28.3.6 The INFORMATION_SCHEMA COLLATIONS Table">
       <code class="literal">
        COLLATIONS
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-collation-character-set-applicability-table.html" title="28.3.7 The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table">
       <code class="literal">
        COLLATION_CHARACTER_SET_APPLICABILITY
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-columns-table.html" title="28.3.8 The INFORMATION_SCHEMA COLUMNS Table">
       <code class="literal">
        COLUMNS
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-column-statistics-table.html" title="28.3.11 The INFORMATION_SCHEMA COLUMN_STATISTICS Table">
       <code class="literal">
        COLUMN_STATISTICS
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-events-table.html" title="28.3.14 The INFORMATION_SCHEMA EVENTS Table">
       <code class="literal">
        EVENTS
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-files-table.html" title="28.3.15 The INFORMATION_SCHEMA FILES Table">
       <code class="literal">
        FILES
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-innodb-columns-table.html" title="28.4.9 The INFORMATION_SCHEMA INNODB_COLUMNS Table">
       <code class="literal">
        INNODB_COLUMNS
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-innodb-datafiles-table.html" title="28.4.10 The INFORMATION_SCHEMA INNODB_DATAFILES Table">
       <code class="literal">
        INNODB_DATAFILES
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-innodb-fields-table.html" title="28.4.11 The INFORMATION_SCHEMA INNODB_FIELDS Table">
       <code class="literal">
        INNODB_FIELDS
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-innodb-foreign-table.html" title="28.4.12 The INFORMATION_SCHEMA INNODB_FOREIGN Table">
       <code class="literal">
        INNODB_FOREIGN
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-innodb-foreign-cols-table.html" title="28.4.13 The INFORMATION_SCHEMA INNODB_FOREIGN_COLS Table">
       <code class="literal">
        INNODB_FOREIGN_COLS
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-innodb-indexes-table.html" title="28.4.20 The INFORMATION_SCHEMA INNODB_INDEXES Table">
       <code class="literal">
        INNODB_INDEXES
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-innodb-tables-table.html" title="28.4.23 The INFORMATION_SCHEMA INNODB_TABLES Table">
       <code class="literal">
        INNODB_TABLES
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-innodb-tablespaces-table.html" title="28.4.24 The INFORMATION_SCHEMA INNODB_TABLESPACES Table">
       <code class="literal">
        INNODB_TABLESPACES
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-innodb-tablespaces-brief-table.html" title="28.4.25 The INFORMATION_SCHEMA INNODB_TABLESPACES_BRIEF Table">
       <code class="literal">
        INNODB_TABLESPACES_BRIEF
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-innodb-tablestats-table.html" title="28.4.26 The INFORMATION_SCHEMA INNODB_TABLESTATS View">
       <code class="literal">
        INNODB_TABLESTATS
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-key-column-usage-table.html" title="28.3.16 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table">
       <code class="literal">
        KEY_COLUMN_USAGE
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       KEYWORDS
      </code>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-parameters-table.html" title="28.3.20 The INFORMATION_SCHEMA PARAMETERS Table">
       <code class="literal">
        PARAMETERS
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-partitions-table.html" title="28.3.21 The INFORMATION_SCHEMA PARTITIONS Table">
       <code class="literal">
        PARTITIONS
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-referential-constraints-table.html" title="28.3.25 The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table">
       <code class="literal">
        REFERENTIAL_CONSTRAINTS
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-resource-groups-table.html" title="28.3.26 The INFORMATION_SCHEMA RESOURCE_GROUPS Table">
       <code class="literal">
        RESOURCE_GROUPS
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-routines-table.html" title="28.3.30 The INFORMATION_SCHEMA ROUTINES Table">
       <code class="literal">
        ROUTINES
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-schemata-table.html" title="28.3.31 The INFORMATION_SCHEMA SCHEMATA Table">
       <code class="literal">
        SCHEMATA
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-statistics-table.html" title="28.3.34 The INFORMATION_SCHEMA STATISTICS Table">
       <code class="literal">
        STATISTICS
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-st-geometry-columns-table.html" title="28.3.35 The INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table">
       <code class="literal">
        ST_GEOMETRY_COLUMNS
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-st-spatial-reference-systems-table.html" title="28.3.36 The INFORMATION_SCHEMA ST_SPATIAL_REFERENCE_SYSTEMS Table">
       <code class="literal">
        ST_SPATIAL_REFERENCE_SYSTEMS
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-tables-table.html" title="28.3.38 The INFORMATION_SCHEMA TABLES Table">
       <code class="literal">
        TABLES
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-table-constraints-table.html" title="28.3.41 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table">
       <code class="literal">
        TABLE_CONSTRAINTS
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-triggers-table.html" title="28.3.44 The INFORMATION_SCHEMA TRIGGERS Table">
       <code class="literal">
        TRIGGERS
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-views-table.html" title="28.3.47 The INFORMATION_SCHEMA VIEWS Table">
       <code class="literal">
        VIEWS
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-view-routine-usage-table.html" title="28.3.48 The INFORMATION_SCHEMA VIEW_ROUTINE_USAGE Table">
       <code class="literal">
        VIEW_ROUTINE_USAGE
       </code>
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="information-schema-view-table-usage-table.html" title="28.3.49 The INFORMATION_SCHEMA VIEW_TABLE_USAGE Table">
       <code class="literal">
        VIEW_TABLE_USAGE
       </code>
      </a>
     </p>
    </li>
   </ul>
  </div>
  <p>
   Queries on those tables are now more efficient because they obtain
      information from data dictionary tables rather than by other,
      slower means. In particular, for each
   <code class="literal">
    INFORMATION_SCHEMA
   </code>
   table that is a view on data
      dictionary tables:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The server no longer must create a temporary table for each
          query of the
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      table.
     </p>
    </li>
    <li class="listitem">
     <p>
      When the underlying data dictionary tables store values
          previously obtained by directory scans (for example, to
          enumerate database names or table names within databases) or
          file-opening operations (for example, to read information from
      <code class="filename">
       .frm
      </code>
      files),
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      queries for those values
          now use table lookups instead. (Additionally, even for a
          non-view
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      table, values
          such as database and table names are retrieved by lookups from
          the data dictionary and do not require directory or file
          scans.)
     </p>
    </li>
    <li class="listitem">
     <p>
      Indexes on the underlying data dictionary tables permit the
          optimizer to construct efficient query execution plans,
          something not true for the previous implementation that
          processed the
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      table
          using a temporary table per query.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The preceding improvements also apply to
   <a class="link" href="show.html" title="15.7.7 SHOW Statements">
    <code class="literal">
     SHOW
    </code>
   </a>
   statements that display
      information corresponding to the
   <code class="literal">
    INFORMATION_SCHEMA
   </code>
   tables that are views on
      data dictionary tables. For example,
   <a class="link" href="show-databases.html" title="15.7.7.15 SHOW DATABASES Statement">
    <code class="literal">
     SHOW
      DATABASES
    </code>
   </a>
   displays the same information as the
   <a class="link" href="information-schema-schemata-table.html" title="28.3.31 The INFORMATION_SCHEMA SCHEMATA Table">
    <code class="literal">
     SCHEMATA
    </code>
   </a>
   table.
  </p>
  <p>
   In addition to the introduction of views on data dictionary
      tables, table statistics contained in the
   <a class="link" href="information-schema-statistics-table.html" title="28.3.34 The INFORMATION_SCHEMA STATISTICS Table">
    <code class="literal">
     STATISTICS
    </code>
   </a>
   and
   <a class="link" href="information-schema-tables-table.html" title="28.3.38 The INFORMATION_SCHEMA TABLES Table">
    <code class="literal">
     TABLES
    </code>
   </a>
   tables is now cached to
      improve
   <a class="link" href="information-schema.html" title="Chapter 28 INFORMATION_SCHEMA Tables">
    <code class="literal">
     INFORMATION_SCHEMA
    </code>
   </a>
   query
      performance. The
   <a class="link" href="server-system-variables.html#sysvar_information_schema_stats_expiry">
    <code class="literal">
     information_schema_stats_expiry
    </code>
   </a>
   system variable defines the period of time before cached table
      statistics expire. The default is 86400 seconds (24 hours). If
      there are no cached statistics or statistics have expired,
      statistics are retrieved from storage engine when querying table
      statistics columns. To update cached values at any time for a
      given table, use
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE TABLE
    </code>
   </a>
  </p>
  <p>
   <a class="link" href="server-system-variables.html#sysvar_information_schema_stats_expiry">
    <code class="literal">
     information_schema_stats_expiry
    </code>
   </a>
   can be set to
   <code class="literal">
    0
   </code>
   to have
   <a class="link" href="information-schema.html" title="Chapter 28 INFORMATION_SCHEMA Tables">
    <code class="literal">
     INFORMATION_SCHEMA
    </code>
   </a>
   queries retrieve
      the latest statistics directly from the storage engine, which is
      not as fast as retrieving cached statistics.
  </p>
  <p>
   For more information, see
   <a class="xref" href="information-schema-optimization.html" title="10.2.3 Optimizing INFORMATION_SCHEMA Queries">
    Section 10.2.3, “Optimizing INFORMATION_SCHEMA Queries”
   </a>
   .
  </p>
  <p>
   <code class="literal">
    INFORMATION_SCHEMA
   </code>
   tables in MySQL
      8.4 are closely tied to the data dictionary,
      resulting in several usage differences. See
   <a class="xref" href="data-dictionary-usage-differences.html" title="16.7 Data Dictionary Usage Differences">
    Section 16.7, “Data Dictionary Usage Differences”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
