/ Check-in [27c65d4d]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

1898
1899
1900
1901
1902
1903
1904




1905
1906
1907
1908
1909
1910
1911
  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->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;







>
>
>
>







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
  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)}
}











finish_test








>
>
>
>
>
>
>
>
>
>

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