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 |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
9c785c58327e08dad94a278a643a5443 |
| User & Date: | drh 2014-05-27 17:57:32.885 |
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: d16e575dac user: drh tags: trunk) | |
| 17:57 | Avoid unnecessary cursor opens and seeks when running the OR-optimization on a WITHOUT ROWID table. (check-in: 9c785c5832 user: drh tags: trunk) | |
| 16:41 | Improved comments on the OR-optimization logic in the query planner. (check-in: 77fef5a398 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
| ︙ | ︙ | |||
3219 3220 3221 3222 3223 3224 3225 |
if( omitTable ){
/* pIdx is a covering index. No need to access the main table. */
}else if( HasRowid(pIdx->pTable) ){
iRowidReg = ++pParse->nMem;
sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, iRowidReg);
sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
sqlite3VdbeAddOp2(v, OP_Seek, iCur, iRowidReg); /* Deferred seek */
| | | 3219 3220 3221 3222 3223 3224 3225 3226 3227 3228 3229 3230 3231 3232 3233 |
if( omitTable ){
/* pIdx is a covering index. No need to access the main table. */
}else if( HasRowid(pIdx->pTable) ){
iRowidReg = ++pParse->nMem;
sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, iRowidReg);
sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
sqlite3VdbeAddOp2(v, OP_Seek, iCur, iRowidReg); /* Deferred seek */
}else if( iCur!=iIdxCur ){
Index *pPk = sqlite3PrimaryKeyIndex(pIdx->pTable);
iRowidReg = sqlite3GetTempRange(pParse, pPk->nKeyCol);
for(j=0; j<pPk->nKeyCol; j++){
k = sqlite3ColumnOfIndex(pIdx, pPk->aiColumn[j]);
sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, k, iRowidReg+j);
}
sqlite3VdbeAddOp4Int(v, OP_NotFound, iCur, addrCont,
|
| ︙ | ︙ | |||
3501 3502 3503 3504 3505 3506 3507 3508 3509 3510 3511 3512 3513 3514 |
** pCov to NULL to indicate that no candidate covering index will
** be available.
*/
pSubLoop = pSubWInfo->a[0].pWLoop;
assert( (pSubLoop->wsFlags & WHERE_AUTO_INDEX)==0 );
if( (pSubLoop->wsFlags & WHERE_INDEXED)!=0
&& (ii==0 || pSubLoop->u.btree.pIndex==pCov)
){
assert( pSubWInfo->a[0].iIdxCur==iCovCur );
pCov = pSubLoop->u.btree.pIndex;
}else{
pCov = 0;
}
| > | 3501 3502 3503 3504 3505 3506 3507 3508 3509 3510 3511 3512 3513 3514 3515 |
** pCov to NULL to indicate that no candidate covering index will
** be available.
*/
pSubLoop = pSubWInfo->a[0].pWLoop;
assert( (pSubLoop->wsFlags & WHERE_AUTO_INDEX)==0 );
if( (pSubLoop->wsFlags & WHERE_INDEXED)!=0
&& (ii==0 || pSubLoop->u.btree.pIndex==pCov)
&& (HasRowid(pTab) || pSubLoop->u.btree.pIndex->autoIndex!=2)
){
assert( pSubWInfo->a[0].iIdxCur==iCovCur );
pCov = pSubLoop->u.btree.pIndex;
}else{
pCov = 0;
}
|
| ︙ | ︙ | |||
6074 6075 6076 6077 6078 6079 6080 |
}
if( pLoop->wsFlags & WHERE_INDEXED ){
Index *pIx = pLoop->u.btree.pIndex;
int iIndexCur;
int op = OP_OpenRead;
/* iIdxCur is always set if to a positive value if ONEPASS is possible */
assert( iIdxCur!=0 || (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 );
| > > > > > > > | > | | | > | 6075 6076 6077 6078 6079 6080 6081 6082 6083 6084 6085 6086 6087 6088 6089 6090 6091 6092 6093 6094 6095 6096 6097 6098 6099 6100 6101 6102 6103 6104 6105 6106 6107 6108 6109 6110 6111 6112 6113 6114 6115 6116 6117 6118 |
}
if( pLoop->wsFlags & WHERE_INDEXED ){
Index *pIx = pLoop->u.btree.pIndex;
int iIndexCur;
int op = OP_OpenRead;
/* iIdxCur is always set if to a positive value if ONEPASS is possible */
assert( iIdxCur!=0 || (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 );
if( !HasRowid(pTab) && pIx->autoIndex==2
&& (wctrlFlags & WHERE_ONETABLE_ONLY)!=0
){
/* This is one term of an OR-optimization using the PRIMARY KEY of a
** WITHOUT ROWID table. No need for a separate index */
iIndexCur = pLevel->iTabCur;
op = 0;
}else if( pWInfo->okOnePass ){
Index *pJ = pTabItem->pTab->pIndex;
iIndexCur = iIdxCur;
assert( wctrlFlags & WHERE_ONEPASS_DESIRED );
while( ALWAYS(pJ) && pJ!=pIx ){
iIndexCur++;
pJ = pJ->pNext;
}
op = OP_OpenWrite;
pWInfo->aiCurOnePass[1] = iIndexCur;
}else if( iIdxCur && (wctrlFlags & WHERE_ONETABLE_ONLY)!=0 ){
iIndexCur = iIdxCur;
}else{
iIndexCur = pParse->nTab++;
}
pLevel->iIdxCur = iIndexCur;
assert( pIx->pSchema==pTab->pSchema );
assert( iIndexCur>=0 );
if( op ){
sqlite3VdbeAddOp3(v, op, iIndexCur, pIx->tnum, iDb);
sqlite3VdbeSetP4KeyInfo(pParse, pIx);
VdbeComment((v, "%s", pIx->zName));
}
}
if( iDb>=0 ) sqlite3CodeVerifySchema(pParse, iDb);
notReady &= ~getMask(&pWInfo->sMaskSet, pTabItem->iCursor);
}
pWInfo->iTop = sqlite3VdbeCurrentAddr(v);
if( db->mallocFailed ) goto whereBeginError;
|
| ︙ | ︙ |