SQLite

Check-in [e8f124284e]
Login

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

Overview
Comment:Use automatic indices on subqueries of the FROM clause when appropriate.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1: e8f124284ee0d0e373dc4431668630f1e17015c1
User & Date: drh 2013-06-17 14:18:21.734
Context
2013-06-17
18:20
Simplifications to the NGQP. Add the queryplantest makefile target. Add testcase() macros in the NGQP. (check-in: eaf1f1b405 user: drh tags: nextgen-query-plan-exp)
14:18
Use automatic indices on subqueries of the FROM clause when appropriate. (check-in: e8f124284e user: drh tags: nextgen-query-plan-exp)
2013-06-15
15:11
Fix compiler warnings. (check-in: 3e8ac46918 user: drh tags: nextgen-query-plan-exp)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
5766
5767
5768
5769
5770
5771
5772
5773
5774
5775
5776
5777
5778
5779
5780
  }

  /* Open all tables in the pTabList and any indices selected for
  ** searching those tables.
  */
  sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */
  notReady = ~(Bitmask)0;
  pWInfo->nRowOut = (WhereCost)1;
  for(ii=0, pLevel=pWInfo->a; ii<nTabList; ii++, pLevel++){
    Table *pTab;     /* Table to open */
    int iDb;         /* Index of database containing table/index */
    struct SrcList_item *pTabItem;
    WhereLoop *pLoop;

    pTabItem = &pTabList->a[pLevel->iFrom];







<







5766
5767
5768
5769
5770
5771
5772

5773
5774
5775
5776
5777
5778
5779
  }

  /* Open all tables in the pTabList and any indices selected for
  ** searching those tables.
  */
  sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */
  notReady = ~(Bitmask)0;

  for(ii=0, pLevel=pWInfo->a; ii<nTabList; ii++, pLevel++){
    Table *pTab;     /* Table to open */
    int iDb;         /* Index of database containing table/index */
    struct SrcList_item *pTabItem;
    WhereLoop *pLoop;

    pTabItem = &pTabList->a[pLevel->iFrom];
Changes to test/autoindex1.test.
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
   ORDER BY x.registering_flock;
} {
  1 0 0 {SCAN TABLE sheep AS s} 
  1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?)} 
  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<?)} 
  0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index} 
  0 1 1 {SCAN SUBQUERY 1 AS y}
}


do_execsql_test autoindex1-700 {
  CREATE TABLE t5(a, b, c);
  EXPLAIN QUERY PLAN SELECT a FROM t5 WHERE b=10 ORDER BY c;
} {







|







247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
   ORDER BY x.registering_flock;
} {
  1 0 0 {SCAN TABLE sheep AS s} 
  1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?)} 
  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<?)} 
  0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index} 
  0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?)}
}


do_execsql_test autoindex1-700 {
  CREATE TABLE t5(a, b, c);
  EXPLAIN QUERY PLAN SELECT a FROM t5 WHERE b=10 ORDER BY c;
} {