/ Check-in [e8b7ea82]
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:Make sure that disabling the covering index scan optimization does not prevent a covering index from being used to satisfy an ORDER BY clause.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1: e8b7ea8202c443bfc8a978588c7d2cfaa14a8fea
User & Date: drh 2013-06-13 17:28:22
Context
2013-06-13
17:58
An index might be useful for ORDER BY if any indexed column is in the ORDER BY clause, not just the first indexed column. check-in: ade473b5 user: drh tags: nextgen-query-plan-exp
17:28
Make sure that disabling the covering index scan optimization does not prevent a covering index from being used to satisfy an ORDER BY clause. check-in: e8b7ea82 user: drh tags: nextgen-query-plan-exp
15:50
Restore the ability to do a BETWEEN query on the rowid. Also fix a nearby comment. check-in: 459a7b90 user: drh tags: nextgen-query-plan-exp
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

   441    441   #define WHERE_IPK          0x00000100  /* x is the INTEGER PRIMARY KEY */
   442    442   #define WHERE_INDEXED      0x00000200  /* WhereLoop.u.btree.pIndex is valid */
   443    443   #define WHERE_VIRTUALTABLE 0x00000400  /* WhereLoop.u.vtab is valid */
   444    444   #define WHERE_IN_ABLE      0x00000800  /* Able to support an IN operator */
   445    445   #define WHERE_ONEROW       0x00001000  /* Selects no more than one row */
   446    446   #define WHERE_MULTI_OR     0x00002000  /* OR using multiple indices */
   447    447   #define WHERE_TEMP_INDEX   0x00004000  /* Uses an ephemeral index */
   448         -#define WHERE_COVER_SCAN   0x00008000  /* Full scan of a covering index */
   449    448   
   450    449   
   451    450   /* Convert a WhereCost value (10 times log2(X)) into its integer value X.
   452    451   ** A rough approximation is used.  The value returned is not exact.
   453    452   */
   454    453   static u64 whereCostToInt(WhereCost x){
   455    454     u64 n;
................................................................................
  3610   3609         pLevel->op = OP_Noop;
  3611   3610       }else if( bRev ){
  3612   3611         pLevel->op = OP_Prev;
  3613   3612       }else{
  3614   3613         pLevel->op = OP_Next;
  3615   3614       }
  3616   3615       pLevel->p1 = iIdxCur;
  3617         -    if( (pLoop->wsFlags & (WHERE_COLUMN_EQ | WHERE_COLUMN_RANGE | 
  3618         -                          WHERE_COLUMN_NULL | WHERE_COLUMN_IN))==0 ){
         3616  +    if( (pLoop->wsFlags & WHERE_CONSTRAINT)==0 ){
  3619   3617         pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;
  3620   3618       }else{
  3621   3619         assert( pLevel->p5==0 );
  3622   3620       }
  3623   3621     }else
  3624   3622   
  3625   3623   #ifndef SQLITE_OMIT_OR_OPTIMIZATION
................................................................................
  4371   4369     Index *pIndex,
  4372   4370     int iCursor
  4373   4371   ){
  4374   4372     ExprList *pOB;
  4375   4373     int iCol;
  4376   4374     int ii;
  4377   4375   
         4376  +  if( pIndex->bUnordered ) return 0;
  4378   4377     if( (pOB = pBuilder->pWInfo->pOrderBy)==0 ) return 0;
  4379   4378     iCol = pIndex->aiColumn[0];
  4380   4379     for(ii=0; ii<pOB->nExpr; ii++){
  4381   4380       Expr *pExpr = sqlite3ExprSkipCollate(pOB->a[ii].pExpr);
  4382   4381       if( pExpr->op!=TK_COLUMN ) return 0;
  4383   4382       if( pExpr->iTable==iCursor ){
  4384   4383         if( pExpr->iColumn==iCol ) return 1;
................................................................................
  4499   4498       pNew->u.btree.nEq = 0;
  4500   4499       pNew->nLTerm = 0;
  4501   4500       pNew->iSortIdx = 0;
  4502   4501       pNew->rSetup = 0;
  4503   4502       pNew->prereq = mExtra;
  4504   4503       pNew->u.btree.pIndex = pProbe;
  4505   4504       b = indexMightHelpWithOrderBy(pBuilder, pProbe, pSrc->iCursor);
         4505  +    /* The ONEPASS_DESIRED flags never occurs together with ORDER BY */
         4506  +    assert( (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 || b==0 );
  4506   4507       if( pProbe->tnum<=0 ){
  4507   4508         /* Integer primary key index */
  4508   4509         pNew->wsFlags = WHERE_IPK;
  4509   4510   
  4510   4511         /* Full table scan */
  4511   4512         pNew->iSortIdx = b ? iSortIdx : 0;
  4512   4513         pNew->nOut = rSize;
................................................................................
  4519   4520         rc = whereLoopInsert(pBuilder, pNew);
  4520   4521         if( rc ) break;
  4521   4522       }else{
  4522   4523         Bitmask m = pSrc->colUsed & ~columnsInIndex(pProbe);
  4523   4524         pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED;
  4524   4525   
  4525   4526         /* Full scan via index */
  4526         -      if( (m==0 || b)
  4527         -       && pProbe->bUnordered==0
  4528         -       && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
  4529         -       && sqlite3GlobalConfig.bUseCis
  4530         -       && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan)
         4527  +      if( b
         4528  +       || ( m==0
         4529  +         && pProbe->bUnordered==0
         4530  +         && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
         4531  +         && sqlite3GlobalConfig.bUseCis
         4532  +         && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan)
         4533  +          )
  4531   4534         ){
  4532   4535           pNew->iSortIdx = b ? iSortIdx : 0;
  4533   4536           pNew->nOut = rSize;
  4534   4537           if( m==0 ){
  4535   4538             /* TUNING: Cost of a covering index scan is 2*(N + log2(N)).
  4536   4539             **  +  The extra 2 factor is to encourage the use of indexed lookups
  4537   4540             **     over index scans.  A table scan uses a factor of 3 so that