<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="information-schema-optimization">
      </a>
      10.2.3 Optimizing INFORMATION_SCHEMA Queries
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045227782048">
  </a>
  <a class="indexterm" name="idm46045227780544">
  </a>
  <p>
   Applications that monitor databases may make frequent use of
   <code class="literal">
    INFORMATION_SCHEMA
   </code>
   tables. To write queries
        for these tables most efficiently, use the following general
        guidelines:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Try to query only
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      tables that are views on data dictionary tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      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.
     </p>
    </li>
   </ul>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    Comparison behavior for database and table names in
    <code class="literal">
     INFORMATION_SCHEMA
    </code>
    queries might differ
          from what you expect. For details, see
    <a class="xref" href="charset-collation-information-schema.html" title="12.8.7 Using Collation in INFORMATION_SCHEMA Searches">
     Section 12.8.7, “Using Collation in INFORMATION_SCHEMA Searches”
    </a>
    .
   </p>
  </div>
  <p>
   These
   <code class="literal">
    INFORMATION_SCHEMA
   </code>
   tables are
        implemented as views on data dictionary tables, so queries on
        them retrieve information from the data dictionary:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa87620233"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">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
VIEW_ROUTINE_USAGE
VIEW_TABLE_USAGE</code></pre>
  </div>
  <p>
   Some types of values, even for a non-view
   <code class="literal">
    INFORMATION_SCHEMA
   </code>
   table, are retrieved by
        lookups from the data dictionary. This includes values such as
        database and table names, table types, and storage engines.
  </p>
  <p>
   Some
   <code class="literal">
    INFORMATION_SCHEMA
   </code>
   tables contain
        columns that provide table statistics:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa81222058"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">STATISTICS.CARDINALITY
TABLES.AUTO_INCREMENT
TABLES.AVG_ROW_LENGTH
TABLES.CHECKSUM
TABLES.CHECK_TIME
TABLES.CREATE_TIME
TABLES.DATA_FREE
TABLES.DATA_LENGTH
TABLES.INDEX_LENGTH
TABLES.MAX_DATA_LENGTH
TABLES.TABLE_ROWS
TABLES.UPDATE_TIME</code></pre>
  </div>
  <p>
   Those columns represent dynamic table metadata; that is,
        information that changes as table contents change.
  </p>
  <p>
   By default, MySQL retrieves cached values for those columns from
        the
   <code class="literal">
    mysql.index_stats
   </code>
   and
   <code class="literal">
    mysql.innodb_table_stats
   </code>
   dictionary tables
        when the columns are queried, which is more efficient than
        retrieving statistics directly from the storage engine. If
        cached statistics are not available or have expired, MySQL
        retrieves the latest statistics from the storage engine and
        caches them in the
   <code class="literal">
    mysql.index_stats
   </code>
   and
   <code class="literal">
    mysql.innodb_table_stats
   </code>
   dictionary tables.
        Subsequent queries retrieve the cached statistics until the
        cached statistics expire. A server restart or the first opening
        of the
   <code class="literal">
    mysql.index_stats
   </code>
   and
   <code class="literal">
    mysql.innodb_table_stats
   </code>
   tables do not update
        cached statistics automatically.
  </p>
  <p>
   The
   <a class="link" href="server-system-variables.html#sysvar_information_schema_stats_expiry">
    <code class="literal">
     information_schema_stats_expiry
    </code>
   </a>
   session variable defines the period of time before cached
        statistics expire. The default is 86400 seconds (24 hours), but
        the time period can be extended to as much as one year.
  </p>
  <p>
   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>
   Querying statistics columns does not store or update statistics
        in the
   <code class="literal">
    mysql.index_stats
   </code>
   and
   <code class="literal">
    mysql.innodb_table_stats
   </code>
   dictionary tables
        under these circumstances:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      When cached statistics have not expired.
     </p>
    </li>
    <li class="listitem">
     <p>
      When
      <a class="link" href="server-system-variables.html#sysvar_information_schema_stats_expiry">
       <code class="literal">
        information_schema_stats_expiry
       </code>
      </a>
      is set to 0.
     </p>
    </li>
    <li class="listitem">
     <p>
      When the server is in
      <a class="link" href="server-system-variables.html#sysvar_read_only">
       <code class="literal">
        read_only
       </code>
      </a>
      ,
      <a class="link" href="server-system-variables.html#sysvar_super_read_only">
       <code class="literal">
        super_read_only
       </code>
      </a>
      ,
      <a class="link" href="server-system-variables.html#sysvar_transaction_read_only">
       <code class="literal">
        transaction_read_only
       </code>
      </a>
      , or
      <a class="link" href="innodb-parameters.html#sysvar_innodb_read_only">
       <code class="literal">
        innodb_read_only
       </code>
      </a>
      mode.
     </p>
    </li>
    <li class="listitem">
     <p>
      When the query also fetches Performance Schema data.
     </p>
    </li>
   </ul>
  </div>
  <p>
   <a class="link" href="server-system-variables.html#sysvar_information_schema_stats_expiry">
    <code class="literal">
     information_schema_stats_expiry
    </code>
   </a>
   is a session variable, and each client session can define its
        own expiration value. Statistics that are retrieved from the
        storage engine and cached by one session are available to other
        sessions.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    If the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_read_only">
     <code class="literal">
      innodb_read_only
     </code>
    </a>
    system variable is enabled,
    <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
     <code class="literal">
      ANALYZE
          TABLE
     </code>
    </a>
    may fail because it cannot update statistics
          tables in the data dictionary, which use
    <code class="literal">
     InnoDB
    </code>
    . For
    <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
     <code class="literal">
      ANALYZE
          TABLE
     </code>
    </a>
    operations that update the key distribution,
          failure may occur even if the operation updates the table
          itself (for example, if it is a
    <code class="literal">
     MyISAM
    </code>
    table). To obtain the updated distribution statistics, set
    <a class="link" href="server-system-variables.html#sysvar_information_schema_stats_expiry">
     <code class="literal">
      information_schema_stats_expiry=0
     </code>
    </a>
    .
   </p>
  </div>
  <p>
   For
   <code class="literal">
    INFORMATION_SCHEMA
   </code>
   tables implemented as
        views on data dictionary tables, indexes on the underlying data
        dictionary tables permit the optimizer to construct efficient
        query execution plans. To see the choices made by the optimizer,
        use
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   . To also see the
        query used by the server to execute an
   <code class="literal">
    INFORMATION_SCHEMA
   </code>
   query, use
   <a class="link" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
    <code class="literal">
     SHOW WARNINGS
    </code>
   </a>
   immediately
        following
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   .
  </p>
  <p>
   Consider this statement, which identifies collations for the
   <code class="literal">
    utf8mb4
   </code>
   character set:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa43829076"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> COLLATION_NAME
       <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>COLLATION_CHARACTER_SET_APPLICABILITY
       <span class="token keyword">WHERE</span> CHARACTER_SET_NAME <span class="token operator">=</span> <span class="token string">'utf8mb4'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> COLLATION_NAME             <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> utf8mb4_general_ci         <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> utf8mb4_bin                <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> utf8mb4_unicode_ci         <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> utf8mb4_icelandic_ci       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> utf8mb4_latvian_ci         <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> utf8mb4_romanian_ci        <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> utf8mb4_slovenian_ci       <span class="token punctuation">|</span></span>
<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
  </div>
  <p>
   How does the server process that statement? To find out, use
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa4207036"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">EXPLAIN</span> <span class="token keyword">SELECT</span> COLLATION_NAME
       <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>COLLATION_CHARACTER_SET_APPLICABILITY
       <span class="token keyword">WHERE</span> CHARACTER_SET_NAME <span class="token operator">=</span> <span class="token string">'utf8mb4'</span>\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
           id<span class="token punctuation">:</span> 1
  select_type<span class="token punctuation">:</span> SIMPLE
        table<span class="token punctuation">:</span> cs
   partitions<span class="token punctuation">:</span> NULL
         type<span class="token punctuation">:</span> const
possible_keys<span class="token punctuation">:</span> PRIMARY,name
          key<span class="token punctuation">:</span> name
      key_len<span class="token punctuation">:</span> 194
          ref<span class="token punctuation">:</span> const
         rows<span class="token punctuation">:</span> 1
     filtered<span class="token punctuation">:</span> 100.00
        Extra<span class="token punctuation">:</span> Using index
<span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 2. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
           id<span class="token punctuation">:</span> 1
  select_type<span class="token punctuation">:</span> SIMPLE
        table<span class="token punctuation">:</span> col
   partitions<span class="token punctuation">:</span> NULL
         type<span class="token punctuation">:</span> ref
possible_keys<span class="token punctuation">:</span> character_set_id
          key<span class="token punctuation">:</span> character_set_id
      key_len<span class="token punctuation">:</span> 8
          ref<span class="token punctuation">:</span> const
         rows<span class="token punctuation">:</span> 68
     filtered<span class="token punctuation">:</span> 100.00
        Extra<span class="token punctuation">:</span> NULL
</span><span class="token output">2 rows in set, 1 warning (0.01 sec)</span></code></pre>
  </div>
  <p>
   To see the query used to satisfy that statement, use
   <a class="link" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
    <code class="literal">
     SHOW WARNINGS
    </code>
   </a>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa17703466"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">WARNINGS</span>\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
  Level<span class="token punctuation">:</span> Note
   Code<span class="token punctuation">:</span> 1003
Message<span class="token punctuation">:</span> /<span class="token punctuation">*</span> select#1 <span class="token punctuation">*</span>/ select `mysql`.`col`.`name` AS `COLLATION_NAME`
         from `mysql`.`character_sets` `cs`
         join `mysql`.`collations` `col`
         where ((`mysql`.`col`.`character_set_id` = '45')
         and ('utf8mb4' = 'utf8mb4'))</span></code></pre>
  </div>
  <p>
   As indicated by
   <a class="link" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
    <code class="literal">
     SHOW WARNINGS
    </code>
   </a>
   ,
        the server handles the query on
   <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>
   as a query on the
   <code class="literal">
    character_sets
   </code>
   and
   <code class="literal">
    collations
   </code>
   data dictionary tables in the
   <code class="literal">
    mysql
   </code>
   system database.
  </p>
 </div>
 <br/>
</div>
