svn commit - mysqldoc@docsrva: r22851 - trunk/refman-5.5

[john.russell]

Author: jrussell
Date: 2010-09-24 22:49:17 +0200 (Fri, 24 Sep 2010)
New Revision: 22851

Log:
More on performance considerations for MEMORY tables, via input from James Day.


Modified:
   trunk/refman-5.5/optimization.xml


Modified: trunk/refman-5.5/optimization.xml
===================================================================
--- trunk/refman-5.5/optimization.xml	2010-09-24 20:12:32 UTC (rev 22850)
+++ trunk/refman-5.5/optimization.xml	2010-09-24 20:49:17 UTC (rev 22851)
Changed blocks: 2, Lines Added: 72, Lines Deleted: 0; 4193 bytes

@@ -1022,6 +1022,43 @@
 
           <listitem>
             <para>
+              Minimize the <literal>OR</literal> keywords in your
+              <literal>WHERE</literal> clauses. If there is no index
+              that helps to locate the values on
+              <emphasis>both</emphasis> sides of the
+              <literal>OR</literal>, any row could potentially be part
+              of the result set, so all rows must be tested, and that
+              requires a full table scan. If you have one index that
+              helps to optimize one side of an <literal>OR</literal>
+              query, and a different index that helps to optimize the
+              other side, use a <literal>UNION</literal> operator to run
+              separate fast queries and merge the results afterward.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              With tables that use the <literal>MEMORY</literal> storage
+              engine, if you run queries that examine ranges of values
+              (using operators such as <literal>&gt;</literal>,
+              <literal>&lt;=</literal>, or <literal>BETWEEN</literal> on
+              the indexed columns), create the index with the
+              <literal>USING BTREE</literal> clause. The default
+              (<literal>USING HASH</literal>) is fast for retrieving
+              individual rows with an equality operator
+              (<literal>=</literal> or <literal>&lt;=&gt;</literal>),
+              but is much slower (requiring a full table scan) to
+              examine a range of column values. A
+              <literal>MEMORY</literal> table created with the
+              <literal>USING BTREE</literal> clause is still fast for
+              equality comparisons, so use that clause for your
+              <literal>MEMORY</literal> tables that handle a variety of
+              queries.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
               Use <literal>ANALYZE TABLE
               <replaceable>tbl_name</replaceable></literal> to update
               the key distributions for the scanned table. See

@@ -5360,6 +5397,41 @@
 
   </section>
 
+  <section id="optimizing-memory-tables">
+
+    <title>Optimizing for <literal>MEMORY</literal> Tables</title>
+
+    <para>
+      The point of <literal>MEMORY</literal> tables is to increase
+      performance for querying data that is read-only or rarely updated.
+      Benchmark your application against equivalent
+      <literal>InnoDB</literal> or <literal>MyISAM</literal> tables
+      under a realistic workload, to confirm that any additional
+      performance is worth the risk of losing data, or the overhead of
+      copying data from a disk-based table at application start.
+    </para>
+
+    <para>
+      The primary speed consideration for <literal>InnoDB</literal>
+      tables is the type you use for each associated index, either a
+      B-tree index or a hash index. On the <literal role="stmt">CREATE
+      INDEX</literal> statement, use the clause <literal>USING
+      BTREE</literal> or <literal>USING HASH</literal>. B-tree indexes
+      are fast for queries do greater-than or less-than comparisons
+      through operators such as <literal>&gt;</literal> or
+      <literal>BETWEEN</literal>. Hash indexes are only fast for queries
+      that look up single values through the <literal>=</literal>
+      operator, or a restricted set of values through the
+      <literal>IN</literal> operator. For why <literal>USING
+      BTREE</literal> is often a better choice than the default
+      <literal>USING HASH</literal>, see
+      <xref linkend="how-to-avoid-table-scan"/>. For implementation
+      details of the different types of <literal>MEMORY</literal>
+      indexes, see <xref linkend="index-btree-hash"/>.
+    </para>
+
+  </section>
+
   <section id="execution-plan-information">
 
     <title>Understanding the Query Execution Plan</title>