Documentation Source Text

Check-in [fee38cf31f]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix typos and clarify the text on the Partial Sorting Using An Index section of queryplanner.html.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: fee38cf31fdde875a5c3605bf22d2c9e151a2c4a
User & Date: drh 2014-05-26 21:43:53
Context
2014-05-26
22:49
Update the download page to support DLL snapshots. Update the change log for 3.8.5. check-in: 3eab08af2c user: drh tags: trunk
21:43
Fix typos and clarify the text on the Partial Sorting Using An Index section of queryplanner.html. check-in: fee38cf31f user: drh tags: trunk
15:38
Talk about partial index sorting in the queryplanner.html document. check-in: f81d69fb6a user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/queryplanner.in.

   673    673   <p>
   674    674   The same basic algorithm is followed, except this time the matching rows
   675    675   of the index are scanned from bottom to top instead of from top to bottom,
   676    676   so that the states will appear in descending order.
   677    677   </p>
   678    678   
   679    679   <tcl>hd_fragment {partialsort}</tcl>
   680         -<h3>3.2 Parital Sorting Using An Index</h3>
          680  +<h3>3.2 Partial Sorting Using An Index</h3>
   681    681   
   682    682   <p>
   683    683   Sometimes only part of an ORDER BY clause an be satisfied using indexes.
   684    684   Consider, for example, the following query:
   685    685   </p>
   686    686   
   687    687   <tcl>
................................................................................
   688    688   code {SELECT * FROM fruitforsale ORDER BY fruit, price}
   689    689   </tcl>
   690    690   
   691    691   <p>
   692    692   If the covering index is used for the scan, the "fruit" column will appear
   693    693   naturally in the correct order, but when there are two or more rows with
   694    694   the same fruit, the price might be out of order.  When this occurs, SQLite
   695         -does many small sorts, one each for each distinct value of fruit, rather
          695  +does many small sorts, one sort for each distinct value of fruit, rather
   696    696   than one large sort.  Figure 22 below illustrates the concept.
   697    697   </p>
   698    698   
   699    699   <tcl>
   700    700   figure 22 #fig22 partial-sort.gif {Partial Sort By Index}
   701    701   </tcl>
   702    702   
          703  +<p>
          704  +In the example, instead of a single sort of 7 elements, there
          705  +are 5 sorts of one-element each and 1 sort of 2 elements for the
          706  +case of fruit=='Orange'.
          707  +
   703    708   <p>
   704    709   The advantages of doing many smaller sorts instead of a single large sort
   705    710   are:
   706    711   <ol>
   707    712   <li>Multiple small sorts collectively use fewer CPU cycles than a single
   708    713       large sort.
   709    714   <li>Each small sort is run independently, meaning that much less information
   710    715       needs to be kept in temporary storage at any one time.
   711         -<li>Output rows can be returned to the application before the table scan
   712         -    is complete.
          716  +<li>Those columns of the ORDER BY that are already in the correct order
          717  +    due to indexes can be omitted from the sort key, further reducing
          718  +    storage requirements and CPU time.
          719  +<li>Output rows can be returned to the application as each small sort
          720  +    completes, and well before the table scan is complete.
   713    721   <li>If a LIMIT clause is present, it might be possible to avoid scanning
   714    722       the entire table.
   715    723   </ol>
   716         -Because of these advantages, SQLite tries to do a partial index sort whenever
   717         -it can.</p>
          724  +Because of these advantages, SQLite always tries to do a partial sort using an
          725  +index even if a complete sort by index is not possible.</p>