<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="optimizing-queries-myisam">
      </a>
      10.6.1 Optimizing MyISAM Queries
     </h3>
    </div>
   </div>
  </div>
  <p>
   Some general tips for speeding up queries on
   <code class="literal">
    MyISAM
   </code>
   tables:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      To help MySQL better optimize queries, use
      <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
       <code class="literal">
        ANALYZE TABLE
       </code>
      </a>
      or run
      <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       <span class="command">
        <strong>
         myisamchk --analyze
        </strong>
       </span>
      </a>
      on a table after it
            has been loaded with data. This updates a value for each
            index part that indicates the average number of rows that
            have the same value. (For unique indexes, this is always 1.)
            MySQL uses this to decide which index to choose when you
            join two tables based on a nonconstant expression. You can
            check the result from the table analysis by using
      <code class="literal">
       SHOW INDEX FROM
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
      </code>
      and examining
            the
      <code class="literal">
       Cardinality
      </code>
      value.
      <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       <span class="command">
        <strong>
         myisamchk
            --description --verbose
        </strong>
       </span>
      </a>
      shows index distribution
            information.
     </p>
    </li>
    <li class="listitem">
     <p>
      To sort an index and data according to an index, use
      <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       <span class="command">
        <strong>
         myisamchk --sort-index --sort-records=1
        </strong>
       </span>
      </a>
      (assuming that you want to sort on index 1). This is a good
            way to make queries faster if you have a unique index from
            which you want to read all rows in order according to the
            index. The first time you sort a large table this way, it
            may take a long time.
     </p>
    </li>
    <li class="listitem">
     <p>
      Try to avoid complex
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      queries on
      <code class="literal">
       MyISAM
      </code>
      tables that are updated
            frequently, to avoid problems with table locking that occur
            due to contention between readers and writers.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       MyISAM
      </code>
      supports concurrent inserts: If a
            table has no free blocks in the middle of the data file, you
            can
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      new rows into it
            at the same time that other threads are reading from the
            table. If it is important to be able to do this, consider
            using the table in ways that avoid deleting rows. Another
            possibility is to run
      <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
       <code class="literal">
        OPTIMIZE
            TABLE
       </code>
      </a>
      to defragment the table after you have
            deleted a lot of rows from it. This behavior is altered by
            setting the
      <a class="link" href="server-system-variables.html#sysvar_concurrent_insert">
       <code class="literal">
        concurrent_insert
       </code>
      </a>
      variable.
            You can force new rows to be appended (and therefore permit
            concurrent inserts), even in tables that have deleted rows.
            See
      <a class="xref" href="concurrent-inserts.html" title="10.11.3 Concurrent Inserts">
       Section 10.11.3, “Concurrent Inserts”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      For
      <code class="literal">
       MyISAM
      </code>
      tables that change frequently,
            try to avoid all variable-length columns
            (
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR
       </code>
      </a>
      ,
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      , and
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      ). The table uses dynamic
            row format if it includes even a single variable-length
            column. See
      <a class="xref" href="storage-engines.html" title="Chapter 18 Alternative Storage Engines">
       Chapter 18,
       <i>
        Alternative Storage Engines
       </i>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      It is normally not useful to split a table into different
            tables just because the rows become large. In accessing a
            row, the biggest performance hit is the disk seek needed to
            find the first byte of the row. After finding the data, most
            modern disks can read the entire row fast enough for most
            applications. The only cases where splitting up a table
            makes an appreciable difference is if it is a
      <code class="literal">
       MyISAM
      </code>
      table using dynamic row format
            that you can change to a fixed row size, or if you very
            often need to scan the table but do not need most of the
            columns. See
      <a class="xref" href="storage-engines.html" title="Chapter 18 Alternative Storage Engines">
       Chapter 18,
       <i>
        Alternative Storage Engines
       </i>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Use
      <code class="literal">
       ALTER TABLE ... ORDER BY
       <em class="replaceable">
        <code>
         expr1
        </code>
       </em>
       ,
       <em class="replaceable">
        <code>
         expr2
        </code>
       </em>
       , ...
      </code>
      if you
            usually retrieve rows in
      <code class="literal">
       <em class="replaceable">
        <code>
         expr1
        </code>
       </em>
       ,
       <em class="replaceable">
        <code>
         expr2
        </code>
       </em>
       , ...
      </code>
      order. By
            using this option after extensive changes to the table, you
            may be able to get higher performance.
     </p>
    </li>
    <li class="listitem">
     <p>
      If you often need to calculate results such as counts based
            on information from a lot of rows, it may be preferable to
            introduce a new table and update the counter in real time.
            An update of the following form is very fast:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa29818268"><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 keyword">UPDATE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">SET</span> <em class="replaceable">count_col</em><span class="token operator">=</span><em class="replaceable">count_col</em><span class="token operator">+</span><span class="token number">1</span> <span class="token keyword">WHERE</span> <em class="replaceable">key_col</em><span class="token operator">=</span><em class="replaceable">constant</em><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      This is very important when you use MySQL storage engines
            such as
      <code class="literal">
       MyISAM
      </code>
      that has only table-level
            locking (multiple readers with single writers). This also
            gives better performance with most database systems, because
            the row locking manager in this case has less to do.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use
      <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
       <code class="literal">
        OPTIMIZE TABLE
       </code>
      </a>
      periodically to avoid fragmentation with dynamic-format
      <code class="literal">
       MyISAM
      </code>
      tables. See
      <a class="xref" href="myisam-table-formats.html" title="18.2.3 MyISAM Table Storage Formats">
       Section 18.2.3, “MyISAM Table Storage Formats”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Declaring a
      <code class="literal">
       MyISAM
      </code>
      table with the
      <code class="literal">
       DELAY_KEY_WRITE=1
      </code>
      table option makes
            index updates faster because they are not flushed to disk
            until the table is closed. The downside is that if something
            kills the server while such a table is open, you must ensure
            that the table is okay by running the server with the
      <a class="link" href="server-system-variables.html#sysvar_myisam_recover_options">
       <code class="literal">
        myisam_recover_options
       </code>
      </a>
      system variable set, or by running
      <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
       <span class="command">
        <strong>
         myisamchk
        </strong>
       </span>
      </a>
      before restarting the server.
            (However, even in this case, you should not lose anything by
            using
      <code class="literal">
       DELAY_KEY_WRITE
      </code>
      , because the key
            information can always be generated from the data rows.)
     </p>
    </li>
    <li class="listitem">
     <p>
      Strings are automatically prefix- and end-space compressed
            in
      <code class="literal">
       MyISAM
      </code>
      indexes. See
      <a class="xref" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
       Section 15.1.15, “CREATE INDEX Statement”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      You can increase performance by caching queries or answers
            in your application and then executing many inserts or
            updates together. Locking the table during this operation
            ensures that the index cache is only flushed once after all
            updates.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
