<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="select-optimization">
      </a>
      10.2.1 Optimizing SELECT Statements
     </h3>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="where-optimization.html">
       10.2.1.1 WHERE Clause Optimization
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="range-optimization.html">
       10.2.1.2 Range Optimization
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="index-merge-optimization.html">
       10.2.1.3 Index Merge Optimization
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="hash-joins.html">
       10.2.1.4 Hash Join Optimization
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="engine-condition-pushdown-optimization.html">
       10.2.1.5 Engine Condition Pushdown Optimization
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="index-condition-pushdown-optimization.html">
       10.2.1.6 Index Condition Pushdown Optimization
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="nested-loop-joins.html">
       10.2.1.7 Nested-Loop Join Algorithms
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="nested-join-optimization.html">
       10.2.1.8 Nested Join Optimization
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="outer-join-optimization.html">
       10.2.1.9 Outer Join Optimization
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="outer-join-simplification.html">
       10.2.1.10 Outer Join Simplification
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="mrr-optimization.html">
       10.2.1.11 Multi-Range Read Optimization
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="bnl-bka-optimization.html">
       10.2.1.12 Block Nested-Loop and Batched Key Access Joins
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="condition-filtering.html">
       10.2.1.13 Condition Filtering
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="constant-folding-optimization.html">
       10.2.1.14 Constant-Folding Optimization
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="is-null-optimization.html">
       10.2.1.15 IS NULL Optimization
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="order-by-optimization.html">
       10.2.1.16 ORDER BY Optimization
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="group-by-optimization.html">
       10.2.1.17 GROUP BY Optimization
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="distinct-optimization.html">
       10.2.1.18 DISTINCT Optimization
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="limit-optimization.html">
       10.2.1.19 LIMIT Query Optimization
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="function-optimization.html">
       10.2.1.20 Function Call Optimization
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="window-function-optimization.html">
       10.2.1.21 Window Function Optimization
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="row-constructor-optimization.html">
       10.2.1.22 Row Constructor Expression Optimization
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="table-scan-avoidance.html">
       10.2.1.23 Avoiding Full Table Scans
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045230367760">
  </a>
  <a class="indexterm" name="idm46045230366304">
  </a>
  <a class="indexterm" name="idm46045230364816">
  </a>
  <p>
   Queries, in the form of
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statements, perform all the lookup operations in the database.
        Tuning these statements is a top priority, whether to achieve
        sub-second response times for dynamic web pages, or to chop
        hours off the time to generate huge overnight reports.
  </p>
  <p>
   Besides
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statements, the
        tuning techniques for queries also apply to constructs such as
   <a class="link" href="create-table-select.html" title="15.1.20.4 CREATE TABLE ... SELECT Statement">
    <code class="literal">
     CREATE
        TABLE...AS SELECT
    </code>
   </a>
   ,
   <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
    <code class="literal">
     INSERT
        INTO...SELECT
    </code>
   </a>
   , and
   <code class="literal">
    WHERE
   </code>
   clauses in
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   statements. Those
        statements have additional performance considerations because
        they combine write operations with the read-oriented query
        operations.
  </p>
  <p>
   NDB Cluster supports a join pushdown optimization whereby a
        qualifying join is sent in its entirety to NDB Cluster data
        nodes, where it can be distributed among them and executed in
        parallel. For more information about this optimization, see
   <a class="xref" href="mysql-cluster-options-variables.html#ndb_join_pushdown-conditions" title="Conditions for NDB pushdown joins">
    Conditions for NDB pushdown joins
   </a>
   .
  </p>
  <p>
   The main considerations for optimizing queries are:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      To make a slow
      <code class="literal">
       SELECT ... WHERE
      </code>
      query
            faster, the first thing to check is whether you can add an
      <a class="link" href="glossary.html#glos_index" title="index">
       index
      </a>
      . Set up indexes on
            columns used in the
      <code class="literal">
       WHERE
      </code>
      clause, to
            speed up evaluation, filtering, and the final retrieval of
            results. To avoid wasted disk space, construct a small set
            of indexes that speed up many related queries used in your
            application.
     </p>
     <p>
      Indexes are especially important for queries that reference
            different tables, using features such as
      <a class="link" href="glossary.html#glos_join" title="join">
       joins
      </a>
      and
      <a class="link" href="glossary.html#glos_foreign_key" title="foreign key">
       foreign keys
      </a>
      . You
            can use the
      <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
       <code class="literal">
        EXPLAIN
       </code>
      </a>
      statement
            to determine which indexes are used for a
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      . See
      <a class="xref" href="mysql-indexes.html" title="10.3.1 How MySQL Uses Indexes">
       Section 10.3.1, “How MySQL Uses Indexes”
      </a>
      and
      <a class="xref" href="using-explain.html" title="10.8.1 Optimizing Queries with EXPLAIN">
       Section 10.8.1, “Optimizing Queries with EXPLAIN”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Isolate and tune any part of the query, such as a function
            call, that takes excessive time. Depending on how the query
            is structured, a function could be called once for every row
            in the result set, or even once for every row in the table,
            greatly magnifying any inefficiency.
     </p>
    </li>
    <li class="listitem">
     <p>
      Minimize the number of
      <a class="link" href="glossary.html#glos_full_table_scan" title="full table scan">
       full table scans
      </a>
      in your queries, particularly for big tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      Keep table statistics up to date by using the
      <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
       <code class="literal">
        ANALYZE TABLE
       </code>
      </a>
      statement
            periodically, so the optimizer has the information needed to
            construct an efficient execution plan.
     </p>
    </li>
    <li class="listitem">
     <p>
      Learn the tuning techniques, indexing techniques, and
            configuration parameters that are specific to the storage
            engine for each table. Both
      <code class="literal">
       InnoDB
      </code>
      and
      <code class="literal">
       MyISAM
      </code>
      have sets of guidelines for
            enabling and sustaining high performance in queries. For
            details, see
      <a class="xref" href="optimizing-innodb-queries.html" title="10.5.6 Optimizing InnoDB Queries">
       Section 10.5.6, “Optimizing InnoDB Queries”
      </a>
      and
      <a class="xref" href="optimizing-queries-myisam.html" title="10.6.1 Optimizing MyISAM Queries">
       Section 10.6.1, “Optimizing MyISAM Queries”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      You can optimize single-query transactions for
      <code class="literal">
       InnoDB
      </code>
      tables, using the technique in
      <a class="xref" href="innodb-performance-ro-txn.html" title="10.5.3 Optimizing InnoDB Read-Only Transactions">
       Section 10.5.3, “Optimizing InnoDB Read-Only Transactions”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Avoid transforming the query in ways that make it hard to
            understand, especially if the optimizer does some of the
            same transformations automatically.
     </p>
    </li>
    <li class="listitem">
     <p>
      If a performance issue is not easily solved by one of the
            basic guidelines, investigate the internal details of the
            specific query by reading the
      <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
       <code class="literal">
        EXPLAIN
       </code>
      </a>
      plan and adjusting
            your indexes,
      <code class="literal">
       WHERE
      </code>
      clauses, join
            clauses, and so on. (When you reach a certain level of
            expertise, reading the
      <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
       <code class="literal">
        EXPLAIN
       </code>
      </a>
      plan might be your
            first step for every query.)
     </p>
    </li>
    <li class="listitem">
     <p>
      Adjust the size and properties of the memory areas that
            MySQL uses for caching. With efficient use of the
      <code class="literal">
       InnoDB
      </code>
      <a class="link" href="glossary.html#glos_buffer_pool" title="buffer pool">
       buffer pool
      </a>
      ,
      <code class="literal">
       MyISAM
      </code>
      key cache, and the MySQL query
            cache, repeated queries run faster because the results are
            retrieved from memory the second and subsequent times.
     </p>
    </li>
    <li class="listitem">
     <p>
      Even for a query that runs fast using the cache memory
            areas, you might still optimize further so that they require
            less cache memory, making your application more scalable.
            Scalability means that your application can handle more
            simultaneous users, larger requests, and so on without
            experiencing a big drop in performance.
     </p>
    </li>
    <li class="listitem">
     <p>
      Deal with locking issues, where the speed of your query
            might be affected by other sessions accessing the tables at
            the same time.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
