List:Commits« Previous MessageNext Message »
From:john.russell Date:August 3 2010 8:52pm
Subject:svn commit - mysqldoc@docsrva: r22061 - trunk/refman-5.5
View as plain text  
Author: jrussell
Date: 2010-08-03 22:52:34 +0200 (Tue, 03 Aug 2010)
New Revision: 22061

Log:
Added outline of major steps for optimizing SELECT statements.
Many of these correspond to later major sections.
This outline suggests which sections could be promoted
to a high level, and which could be moved later because
they are background information for some other subject.
(For example, "Make Your Data as Small as Possible"
is relevant for queries but is a subsection much later
in the chapter. Good candidate to move next.)


Modified:
   trunk/refman-5.5/optimization.xml


Modified: trunk/refman-5.5/optimization.xml
===================================================================
--- trunk/refman-5.5/optimization.xml	2010-08-03 19:59:44 UTC (rev 22060)
+++ trunk/refman-5.5/optimization.xml	2010-08-03 20:52:34 UTC (rev 22061)
Changed blocks: 1, Lines Added: 89, Lines Deleted: 0; 3586 bytes

@@ -664,6 +664,95 @@
         hours off the time to generate huge overnight reports.
       </para>
 
+      <para>
+        The main considerations for optimizing queries are:
+      </para>
+
+      <itemizedlist>
+
+        <listitem>
+          <para>
+            Isolate and tune any part of the query, such as a function
+            call, that takes excessive time.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            Set up indexes on columns used in the
+            <literal>WHERE</literal> 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.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            Minimize the number of table scans in your queries,
+            particularly for big tables.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            Keep table statistics up to date, so the optimizer has the
+            information needed to construct an efficient execution plan.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            Avoid transforming the query in ways that make it hard to
+            understand, especially if the optimizer does some of the
+            same transformations automatically.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            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 <literal>EXPLAIN</literal>
+            plan and adjusting your indexes, <literal>WHERE</literal>
+            clauses, join clauses, and so on. (When you reach a certain
+            level of expertise, reading the <literal>EXPLAIN</literal>
+            plan might be your first step for every query.)
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            Adjust the size and properties of the memory areas that
+            MySQL uses for caching. With efficient use of the
+            <literal>InnoDB</literal> buffer pool,
+            <literal>MyISAM</literal> key cache, and the MySQL query
+            cache, repeated queries run faster because the results are
+            retrieved from memory the second and subsequent times.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            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.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            Deal with locking issues, where the speed of your query
+            might be affected by other sessions accessing the tables at
+            the same time.
+          </para>
+        </listitem>
+
+      </itemizedlist>
+
       <section id="select-benchmarking">
 
         <title>Measuring the Speed of Expressions and Functions</title>


Thread
svn commit - mysqldoc@docsrva: r22061 - trunk/refman-5.5john.russell3 Aug