/ Check-in [27c65d4d]
Login

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

Overview
Comment:Ensure that automatic indexes are only created in scenarios where they may be used more than once.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 27c65d4d9c58bfc4ea8f9337fa15090459fb26c5
User & Date: dan 2011-07-02 15:32:57
References
2011-07-02
15:42
Cherrypick change [27c65d4d9c] into the 3.7.2 branch. check-in: 9bbcd8c0 user: dan tags: branch-3.7.2
Context
2011-07-02
19:12
Add a testcase macro to ensure testing a boundary case in DISTINCT processing. check-in: ff9fc722 user: drh tags: trunk
15:32
Ensure that automatic indexes are only created in scenarios where they may be used more than once. check-in: 27c65d4d user: dan tags: trunk
09:46
Merge experimental changes improving optimization of DISTINCT queries with the trunk. check-in: 45e581bf user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  1898   1898     double nTableRow;           /* Rows in the input table */
  1899   1899     double logN;                /* log(nTableRow) */
  1900   1900     double costTempIdx;         /* per-query cost of the transient index */
  1901   1901     WhereTerm *pTerm;           /* A single term of the WHERE clause */
  1902   1902     WhereTerm *pWCEnd;          /* End of pWC->a[] */
  1903   1903     Table *pTable;              /* Table tht might be indexed */
  1904   1904   
         1905  +  if( pParse->nQueryLoop<=(double)1 ){
         1906  +    /* There is no point in building an automatic index for a single scan */
         1907  +    return;
         1908  +  }
  1905   1909     if( (pParse->db->flags & SQLITE_AutoIndex)==0 ){
  1906   1910       /* Automatic indices are disabled at run-time */
  1907   1911       return;
  1908   1912     }
  1909   1913     if( (pCost->plan.wsFlags & WHERE_NOT_FULLSCAN)!=0 ){
  1910   1914       /* We already have some kind of index in use for this query. */
  1911   1915       return;

Changes to test/autoindex1.test.

   243    243     1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)} 
   244    244     1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 rows)} 
   245    245     1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 
   246    246     2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?) (~1 rows)} 
   247    247     0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index (~1000000 rows)} 
   248    248     0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?) (~8 rows)}
   249    249   }
          250  +
          251  +
          252  +do_execsql_test autoindex1-700 {
          253  +  CREATE TABLE t5(a, b, c);
          254  +  EXPLAIN QUERY PLAN SELECT a FROM t5 WHERE b=10 ORDER BY c;
          255  +} {
          256  +  0 0 0 {SCAN TABLE t5 (~100000 rows)} 
          257  +  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
          258  +}
          259  +
   250    260   
   251    261   finish_test