<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="estimating-performance">
      </a>
      10.8.5 Estimating Query Performance
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045224564912">
  </a>
  <a class="indexterm" name="idm46045224563456">
  </a>
  <a class="indexterm" name="idm46045224561968">
  </a>
  <p>
   In most cases, you can estimate query performance by counting
        disk seeks. For small tables, you can usually find a row in one
        disk seek (because the index is probably cached). For bigger
        tables, you can estimate that, using B-tree indexes, you need
        this many seeks to find a row:
   <code class="literal">
    log(
    <em class="replaceable">
     <code>
      row_count
     </code>
    </em>
    ) /
        log(
    <em class="replaceable">
     <code>
      index_block_length
     </code>
    </em>
    / 3 * 2 /
        (
    <em class="replaceable">
     <code>
      index_length
     </code>
    </em>
    +
    <em class="replaceable">
     <code>
      data_pointer_length
     </code>
    </em>
    )) + 1
   </code>
   .
  </p>
  <p>
   In MySQL, an index block is usually 1,024 bytes and the data
        pointer is usually four bytes. For a 500,000-row table with a
        key value length of three bytes (the size of
   <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
    <code class="literal">
     MEDIUMINT
    </code>
   </a>
   ), the formula indicates
   <code class="literal">
    log(500,000)/log(1024/3*2/(3+4)) + 1
   </code>
   =
   <code class="literal">
    4
   </code>
   seeks.
  </p>
  <p>
   This index would require storage of about 500,000 * 7 * 3/2 =
        5.2MB (assuming a typical index buffer fill ratio of 2/3), so
        you probably have much of the index in memory and so need only
        one or two calls to read data to find the row.
  </p>
  <p>
   For writes, however, you need four seek requests to find where
        to place a new index value and normally two seeks to update the
        index and write the row.
  </p>
  <p>
   The preceding discussion does not mean that your application
        performance slowly degenerates by log
   <em class="replaceable">
    <code>
     N
    </code>
   </em>
   . As long as everything is cached by
        the OS or the MySQL server, things become only marginally slower
        as the table gets bigger. After the data gets too big to be
        cached, things start to go much slower until your applications
        are bound only by disk seeks (which increase by log
   <em class="replaceable">
    <code>
     N
    </code>
   </em>
   ). To avoid this, increase the key
        cache size as the data grows. For
   <code class="literal">
    MyISAM
   </code>
   tables, the key cache size is controlled by the
   <a class="link" href="server-system-variables.html#sysvar_key_buffer_size">
    <code class="literal">
     key_buffer_size
    </code>
   </a>
   system
        variable. See
   <a class="xref" href="server-configuration.html" title="7.1.1 Configuring the Server">
    Section 7.1.1, “Configuring the Server”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
