/ Check-in [32b3d110]
Login

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

Overview
Comment:Experimental syntax enhancement for an "INDEXED" keyword following a FROM-clause subquery that indicates that an automatic index on that subquery is suggested.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | exp-indexed-clause
Files: files | file ages | folders
SHA3-256: 32b3d1105074529548627dcd63e25fbec6ba5e453b00e03f31ab2e324cf7f38d
User & Date: drh 2018-05-14 17:12:42
Context
2018-05-14
18:09
The restriction on using automatic indexes for low cardinality loops only applies if the cardinality is restricted by a LIMIT clause. Closed-Leaf check-in: 991a76bf user: drh tags: exp-indexed-clause
17:12
Experimental syntax enhancement for an "INDEXED" keyword following a FROM-clause subquery that indicates that an automatic index on that subquery is suggested. check-in: 32b3d110 user: drh tags: exp-indexed-clause
15:26
Convert the schema creation logic in the rtree extension to use the new sqlite3_str interface. check-in: fd8b8c41 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

  3923   3923     if( p && pIndexedBy->n>0 ){
  3924   3924       struct SrcList_item *pItem;
  3925   3925       assert( p->nSrc>0 );
  3926   3926       pItem = &p->a[p->nSrc-1];
  3927   3927       assert( pItem->fg.notIndexed==0 );
  3928   3928       assert( pItem->fg.isIndexedBy==0 );
  3929   3929       assert( pItem->fg.isTabFunc==0 );
  3930         -    if( pIndexedBy->n==1 && !pIndexedBy->z ){
  3931         -      /* A "NOT INDEXED" clause was supplied. See parse.y 
  3932         -      ** construct "indexed_opt" for details. */
  3933         -      pItem->fg.notIndexed = 1;
         3930  +    if( pIndexedBy->n>=1 && !pIndexedBy->z ){
         3931  +      if( pIndexedBy->n==1 ){
         3932  +        pItem->fg.notIndexed = 1;
         3933  +      }else{
         3934  +        assert( pIndexedBy->n==2 );
         3935  +        pItem->fg.useAutoIdx = 1;
         3936  +      }
  3934   3937       }else{
  3935   3938         pItem->u1.zIndexedBy = sqlite3NameFromToken(pParse->db, pIndexedBy);
  3936   3939         pItem->fg.isIndexedBy = 1;
  3937   3940       }
  3938   3941     }
  3939   3942   }
  3940   3943   

Changes to src/parse.y.

   648    648   seltablist(A) ::= stl_prefix(A) nm(Y) dbnm(D) LP exprlist(E) RP as(Z)
   649    649                     on_opt(N) using_opt(U). {
   650    650     A = sqlite3SrcListAppendFromTerm(pParse,A,&Y,&D,&Z,0,N,U);
   651    651     sqlite3SrcListFuncArgs(pParse, A, E);
   652    652   }
   653    653   %ifndef SQLITE_OMIT_SUBQUERY
   654    654     seltablist(A) ::= stl_prefix(A) LP select(S) RP
   655         -                    as(Z) on_opt(N) using_opt(U). {
          655  +                    as(Z) indexed_opt(I) on_opt(N) using_opt(U). {
   656    656       A = sqlite3SrcListAppendFromTerm(pParse,A,0,0,&Z,S,N,U);
          657  +    sqlite3SrcListIndexedBy(pParse, A, &I);
   657    658     }
   658    659     seltablist(A) ::= stl_prefix(A) LP seltablist(F) RP
   659         -                    as(Z) on_opt(N) using_opt(U). {
          660  +                    as(Z) indexed_opt(I) on_opt(N) using_opt(U). {
   660    661       if( A==0 && Z.n==0 && N==0 && U==0 ){
   661    662         A = F;
   662    663       }else if( F->nSrc==1 ){
   663    664         A = sqlite3SrcListAppendFromTerm(pParse,A,0,0,&Z,0,N,U);
   664    665         if( A ){
   665    666           struct SrcList_item *pNew = &A->a[A->nSrc-1];
   666    667           struct SrcList_item *pOld = F->a;
................................................................................
   673    674         sqlite3SrcListDelete(pParse->db, F);
   674    675       }else{
   675    676         Select *pSubquery;
   676    677         sqlite3SrcListShiftJoinType(F);
   677    678         pSubquery = sqlite3SelectNew(pParse,0,F,0,0,0,0,SF_NestedFrom,0);
   678    679         A = sqlite3SrcListAppendFromTerm(pParse,A,0,0,&Z,pSubquery,N,U);
   679    680       }
          681  +    sqlite3SrcListIndexedBy(pParse, A, &I);
   680    682     }
   681    683   %endif  SQLITE_OMIT_SUBQUERY
   682    684   
   683    685   %type dbnm {Token}
   684    686   dbnm(A) ::= .          {A.z=0; A.n=0;}
   685    687   dbnm(A) ::= DOT nm(X). {A = X;}
   686    688   
................................................................................
   747    749   // normally illegal. The sqlite3SrcListIndexedBy() function 
   748    750   // recognizes and interprets this as a special case.
   749    751   //
   750    752   %type indexed_opt {Token}
   751    753   indexed_opt(A) ::= .                 {A.z=0; A.n=0;}
   752    754   indexed_opt(A) ::= INDEXED BY nm(X). {A = X;}
   753    755   indexed_opt(A) ::= NOT INDEXED.      {A.z=0; A.n=1;}
          756  +indexed_opt(A) ::= INDEXED.          {A.z=0; A.n=2;}
   754    757   
   755    758   %type using_opt {IdList*}
   756    759   %destructor using_opt {sqlite3IdListDelete(pParse->db, $$);}
   757    760   using_opt(U) ::= USING LP idlist(L) RP.  {U = L;}
   758    761   using_opt(U) ::= .                        {U = 0;}
   759    762   
   760    763   

Changes to src/sqliteInt.h.

  2604   2604         u8 jointype;      /* Type of join between this table and the previous */
  2605   2605         unsigned notIndexed :1;    /* True if there is a NOT INDEXED clause */
  2606   2606         unsigned isIndexedBy :1;   /* True if there is an INDEXED BY clause */
  2607   2607         unsigned isTabFunc :1;     /* True if table-valued-function syntax */
  2608   2608         unsigned isCorrelated :1;  /* True if sub-query is correlated */
  2609   2609         unsigned viaCoroutine :1;  /* Implemented as a co-routine */
  2610   2610         unsigned isRecursive :1;   /* True for recursive reference in WITH */
         2611  +      unsigned useAutoIdx  :1;   /* Use an automatic index if possible */
  2611   2612       } fg;
  2612   2613       int iCursor;      /* The VDBE cursor number used to access this table */
  2613   2614       Expr *pOn;        /* The ON clause of a join */
  2614   2615       IdList *pUsing;   /* The USING clause of a join */
  2615   2616       Bitmask colUsed;  /* Bit N (1<<N) set if column N of pTab is used */
  2616   2617       union {
  2617   2618         char *zIndexedBy;    /* Identifier from "INDEXED BY <zIndex>" clause */

Changes to src/where.c.

  2868   2868           ** those objects, since there is no opportunity to add schema
  2869   2869           ** indexes on subqueries and views. */
  2870   2870           pNew->rSetup = rLogSize + rSize + 4;
  2871   2871           if( pTab->pSelect==0 && (pTab->tabFlags & TF_Ephemeral)==0 ){
  2872   2872             pNew->rSetup += 24;
  2873   2873           }
  2874   2874           ApplyCostMultiplier(pNew->rSetup, pTab->costMult);
  2875         -        if( pNew->rSetup<0 ) pNew->rSetup = 0;
         2875  +        if( pNew->rSetup<0 || pSrc->fg.useAutoIdx ) pNew->rSetup = 0;
  2876   2876           /* TUNING: Each index lookup yields 20 rows in the table.  This
  2877   2877           ** is more than the usual guess of 10 rows, since we have no way
  2878   2878           ** of knowing how selective the index will ultimately be.  It would
  2879   2879           ** not be unreasonable to make this value much larger. */
  2880   2880           pNew->nOut = 43;  assert( 43==sqlite3LogEst(20) );
  2881   2881           pNew->rRun = sqlite3LogEstAdd(rLogSize,pNew->nOut);
  2882   2882           pNew->wsFlags = WHERE_AUTO_INDEX;