<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="performance-schema-optimization">
      </a>
      10.2.4 Optimizing Performance Schema Queries
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045227708448">
  </a>
  <a class="indexterm" name="idm46045227706944">
  </a>
  <p>
   Applications that monitor databases may make frequent use of
        Performance Schema tables. To write queries for these tables
        most efficiently, take advantage of their indexes. For example,
        include a
   <code class="literal">
    WHERE
   </code>
   clause that restricts
        retrieved rows based on comparison to specific values in an
        indexed column.
  </p>
  <p>
   Most Performance Schema tables have indexes. Tables that do not
        are those that normally contain few rows or are unlikely to be
        queried frequently. Performance Schema indexes give the
        optimizer access to execution plans other than full table scans.
        These indexes also improve performance for related objects, such
        as
   <a class="link" href="sys-schema.html" title="Chapter 30 MySQL sys Schema">
    <code class="literal">
     sys
    </code>
   </a>
   schema views that use those
        tables.
  </p>
  <p>
   To see whether a given Performance Schema table has indexes and
        what they are, use
   <a class="link" href="show-index.html" title="15.7.7.23 SHOW INDEX Statement">
    <code class="literal">
     SHOW INDEX
    </code>
   </a>
   or
   <a class="link" href="show-create-table.html" title="15.7.7.11 SHOW CREATE TABLE Statement">
    <code class="literal">
     SHOW CREATE TABLE
    </code>
   </a>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa2690056"><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">INDEX</span> <span class="token keyword">FROM</span> performance_schema<span class="token punctuation">.</span>accounts\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>
        Table<span class="token punctuation">:</span> accounts
   Non_unique<span class="token punctuation">:</span> 0
     Key_name<span class="token punctuation">:</span> ACCOUNT
 Seq_in_index<span class="token punctuation">:</span> 1
  Column_name<span class="token punctuation">:</span> USER
    Collation<span class="token punctuation">:</span> NULL
  Cardinality<span class="token punctuation">:</span> NULL
     Sub_part<span class="token punctuation">:</span> NULL
       Packed<span class="token punctuation">:</span> NULL
         Null<span class="token punctuation">:</span> YES
   Index_type<span class="token punctuation">:</span> HASH
      Comment<span class="token punctuation">:</span>
Index_comment<span class="token punctuation">:</span>
      Visible<span class="token punctuation">:</span> YES
<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>
        Table<span class="token punctuation">:</span> accounts
   Non_unique<span class="token punctuation">:</span> 0
     Key_name<span class="token punctuation">:</span> ACCOUNT
 Seq_in_index<span class="token punctuation">:</span> 2
  Column_name<span class="token punctuation">:</span> HOST
    Collation<span class="token punctuation">:</span> NULL
  Cardinality<span class="token punctuation">:</span> NULL
     Sub_part<span class="token punctuation">:</span> NULL
       Packed<span class="token punctuation">:</span> NULL
         Null<span class="token punctuation">:</span> YES
   Index_type<span class="token punctuation">:</span> HASH
      Comment<span class="token punctuation">:</span>
Index_comment<span class="token punctuation">:</span>
      Visible<span class="token punctuation">:</span> YES
</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> performance_schema<span class="token punctuation">.</span>rwlock_instances\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>
       Table<span class="token punctuation">:</span> rwlock_instances
Create Table<span class="token punctuation">:</span> CREATE TABLE `rwlock_instances` (
  `NAME` varchar(128) NOT NULL,
  `OBJECT_INSTANCE_BEGIN` bigint(20) unsigned NOT NULL,
  `WRITE_LOCKED_BY_THREAD_ID` bigint(20) unsigned DEFAULT NULL,
  `READ_LOCKED_BY_COUNT` int(10) unsigned NOT NULL,
  PRIMARY KEY (`OBJECT_INSTANCE_BEGIN`),
  KEY `NAME` (`NAME`),
  KEY `WRITE_LOCKED_BY_THREAD_ID` (`WRITE_LOCKED_BY_THREAD_ID`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci</span></code></pre>
  </div>
  <p>
   To see the execution plan for a Performance Schema query and
        whether it uses any indexes, 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="sa2406345"><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> <span class="token operator">*</span> <span class="token keyword">FROM</span> performance_schema<span class="token punctuation">.</span>accounts
       <span class="token keyword">WHERE</span> <span class="token punctuation">(</span><span class="token keyword">USER</span><span class="token punctuation">,</span><span class="token keyword">HOST</span><span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token string">'root'</span><span class="token punctuation">,</span><span class="token string">'localhost'</span><span class="token punctuation">)</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> accounts
   partitions<span class="token punctuation">:</span> NULL
         type<span class="token punctuation">:</span> const
possible_keys<span class="token punctuation">:</span> ACCOUNT
          key<span class="token punctuation">:</span> ACCOUNT
      key_len<span class="token punctuation">:</span> 278
          ref<span class="token punctuation">:</span> const,const
         rows<span class="token punctuation">:</span> 1
     filtered<span class="token punctuation">:</span> 100.00
        Extra<span class="token punctuation">:</span> NULL</span></code></pre>
  </div>
  <p>
   The
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   output indicates that
        the optimizer uses the
   <a class="link" href="performance-schema-accounts-table.html" title="29.12.8.1 The accounts Table">
    <code class="literal">
     accounts
    </code>
   </a>
   table
   <code class="literal">
    ACCOUNT
   </code>
   index that comprises the
   <code class="literal">
    USER
   </code>
   and
   <code class="literal">
    HOST
   </code>
   columns.
  </p>
  <p>
   Performance Schema indexes are virtual: They are a construct of
        the Performance Schema storage engine and use no memory or disk
        storage. The Performance Schema reports index information to the
        optimizer so that it can construct efficient execution plans.
        The Performance Schema in turn uses optimizer information about
        what to look for (for example, a particular key value), so that
        it can perform efficient lookups without building actual index
        structures. This implementation provides two important benefits:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      It entirely avoids the maintenance cost normally incurred
            for tables that undergo frequent updates.
     </p>
    </li>
    <li class="listitem">
     <p>
      It reduces at an early stage of query execution the amount
            of data retrieved. For conditions on the indexed columns,
            the Performance Schema efficiently returns only table rows
            that satisfy the query conditions. Without an index, the
            Performance Schema would return all rows in the table,
            requiring that the optimizer later evaluate the conditions
            against each row to produce the final result.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Performance Schema indexes are predefined and cannot be dropped,
        added, or altered.
  </p>
  <p>
   Performance Schema indexes are similar to hash indexes. For
        example:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      They are used only for equality comparisons that use the
      <code class="literal">
       =
      </code>
      or
      <code class="literal">
       &lt;=&gt;
      </code>
      operators.
     </p>
    </li>
    <li class="listitem">
     <p>
      They are unordered. If a query result must have specific row
            ordering characteristics, include an
      <code class="literal">
       ORDER
            BY
      </code>
      clause.
     </p>
    </li>
   </ul>
  </div>
  <p>
   For additional information about hash indexes, see
   <a class="xref" href="index-btree-hash.html" title="10.3.9 Comparison of B-Tree and Hash Indexes">
    Section 10.3.9, “Comparison of B-Tree and Hash Indexes”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
