/ Check-in [9c785c58]
Login

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

Overview
Comment:Avoid unnecessary cursor opens and seeks when running the OR-optimization on a WITHOUT ROWID table.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9c785c58327e08dad94a278a643a5443f94aec36
User & Date: drh 2014-05-27 17:57:32
Context
2014-05-27
18:18
Change the name of the Index.autoIndex field to Index.idxType and provide symbolic names for the various values of that field rather than using magic numbers. check-in: d16e575d user: drh tags: trunk
17:57
Avoid unnecessary cursor opens and seeks when running the OR-optimization on a WITHOUT ROWID table. check-in: 9c785c58 user: drh tags: trunk
16:41
Improved comments on the OR-optimization logic in the query planner. check-in: 77fef5a3 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  3219   3219       if( omitTable ){
  3220   3220         /* pIdx is a covering index.  No need to access the main table. */
  3221   3221       }else if( HasRowid(pIdx->pTable) ){
  3222   3222         iRowidReg = ++pParse->nMem;
  3223   3223         sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, iRowidReg);
  3224   3224         sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
  3225   3225         sqlite3VdbeAddOp2(v, OP_Seek, iCur, iRowidReg);  /* Deferred seek */
  3226         -    }else{
         3226  +    }else if( iCur!=iIdxCur ){
  3227   3227         Index *pPk = sqlite3PrimaryKeyIndex(pIdx->pTable);
  3228   3228         iRowidReg = sqlite3GetTempRange(pParse, pPk->nKeyCol);
  3229   3229         for(j=0; j<pPk->nKeyCol; j++){
  3230   3230           k = sqlite3ColumnOfIndex(pIdx, pPk->aiColumn[j]);
  3231   3231           sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, k, iRowidReg+j);
  3232   3232         }
  3233   3233         sqlite3VdbeAddOp4Int(v, OP_NotFound, iCur, addrCont,
................................................................................
  3501   3501             ** pCov to NULL to indicate that no candidate covering index will 
  3502   3502             ** be available.
  3503   3503             */
  3504   3504             pSubLoop = pSubWInfo->a[0].pWLoop;
  3505   3505             assert( (pSubLoop->wsFlags & WHERE_AUTO_INDEX)==0 );
  3506   3506             if( (pSubLoop->wsFlags & WHERE_INDEXED)!=0
  3507   3507              && (ii==0 || pSubLoop->u.btree.pIndex==pCov)
         3508  +           && (HasRowid(pTab) || pSubLoop->u.btree.pIndex->autoIndex!=2)
  3508   3509             ){
  3509   3510               assert( pSubWInfo->a[0].iIdxCur==iCovCur );
  3510   3511               pCov = pSubLoop->u.btree.pIndex;
  3511   3512             }else{
  3512   3513               pCov = 0;
  3513   3514             }
  3514   3515   
................................................................................
  6074   6075       }
  6075   6076       if( pLoop->wsFlags & WHERE_INDEXED ){
  6076   6077         Index *pIx = pLoop->u.btree.pIndex;
  6077   6078         int iIndexCur;
  6078   6079         int op = OP_OpenRead;
  6079   6080         /* iIdxCur is always set if to a positive value if ONEPASS is possible */
  6080   6081         assert( iIdxCur!=0 || (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 );
  6081         -      if( pWInfo->okOnePass ){
         6082  +      if( !HasRowid(pTab) && pIx->autoIndex==2
         6083  +       && (wctrlFlags & WHERE_ONETABLE_ONLY)!=0
         6084  +      ){
         6085  +        /* This is one term of an OR-optimization using the PRIMARY KEY of a
         6086  +        ** WITHOUT ROWID table.  No need for a separate index */
         6087  +        iIndexCur = pLevel->iTabCur;
         6088  +        op = 0;
         6089  +      }else if( pWInfo->okOnePass ){
  6082   6090           Index *pJ = pTabItem->pTab->pIndex;
  6083   6091           iIndexCur = iIdxCur;
  6084   6092           assert( wctrlFlags & WHERE_ONEPASS_DESIRED );
  6085   6093           while( ALWAYS(pJ) && pJ!=pIx ){
  6086   6094             iIndexCur++;
  6087   6095             pJ = pJ->pNext;
  6088   6096           }
................................................................................
  6092   6100           iIndexCur = iIdxCur;
  6093   6101         }else{
  6094   6102           iIndexCur = pParse->nTab++;
  6095   6103         }
  6096   6104         pLevel->iIdxCur = iIndexCur;
  6097   6105         assert( pIx->pSchema==pTab->pSchema );
  6098   6106         assert( iIndexCur>=0 );
  6099         -      sqlite3VdbeAddOp3(v, op, iIndexCur, pIx->tnum, iDb);
  6100         -      sqlite3VdbeSetP4KeyInfo(pParse, pIx);
  6101         -      VdbeComment((v, "%s", pIx->zName));
         6107  +      if( op ){
         6108  +        sqlite3VdbeAddOp3(v, op, iIndexCur, pIx->tnum, iDb);
         6109  +        sqlite3VdbeSetP4KeyInfo(pParse, pIx);
         6110  +        VdbeComment((v, "%s", pIx->zName));
         6111  +      }
  6102   6112       }
  6103   6113       if( iDb>=0 ) sqlite3CodeVerifySchema(pParse, iDb);
  6104   6114       notReady &= ~getMask(&pWInfo->sMaskSet, pTabItem->iCursor);
  6105   6115     }
  6106   6116     pWInfo->iTop = sqlite3VdbeCurrentAddr(v);
  6107   6117     if( db->mallocFailed ) goto whereBeginError;
  6108   6118