SQLite

Check-in [27c65d4d9c]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 27c65d4d9c58bfc4ea8f9337fa15090459fb26c5
User & Date: dan 2011-07-02 15:32:57.635
References
2011-07-02
15:42
Cherrypick change [27c65d4d9c] into the 3.7.2 branch. (check-in: 9bbcd8c076 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: ff9fc722dc 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: 27c65d4d9c user: dan tags: trunk)
09:46
Merge experimental changes improving optimization of DISTINCT queries with the trunk. (check-in: 45e581bff7 user: dan tags: trunk)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/where.c.
1898
1899
1900
1901
1902
1903
1904




1905
1906
1907
1908
1909
1910
1911
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915







+
+
+
+







  double nTableRow;           /* Rows in the input table */
  double logN;                /* log(nTableRow) */
  double costTempIdx;         /* per-query cost of the transient index */
  WhereTerm *pTerm;           /* A single term of the WHERE clause */
  WhereTerm *pWCEnd;          /* End of pWC->a[] */
  Table *pTable;              /* Table tht might be indexed */

  if( pParse->nQueryLoop<=(double)1 ){
    /* There is no point in building an automatic index for a single scan */
    return;
  }
  if( (pParse->db->flags & SQLITE_AutoIndex)==0 ){
    /* Automatic indices are disabled at run-time */
    return;
  }
  if( (pCost->plan.wsFlags & WHERE_NOT_FULLSCAN)!=0 ){
    /* We already have some kind of index in use for this query. */
    return;
Changes to test/autoindex1.test.
243
244
245
246
247
248
249










250
251
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261







+
+
+
+
+
+
+
+
+
+


  1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)} 
  1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 rows)} 
  1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 
  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)} 
  0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index (~1000000 rows)} 
  0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?) (~8 rows)}
}


do_execsql_test autoindex1-700 {
  CREATE TABLE t5(a, b, c);
  EXPLAIN QUERY PLAN SELECT a FROM t5 WHERE b=10 ORDER BY c;
} {
  0 0 0 {SCAN TABLE t5 (~100000 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}


finish_test