Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Demonstration code on a possible technique for optimizing the use of IN operator on columns to the right of multicolumn indexes. If the OP_Noop generated where were really a new opcode that checked to see if there existed any entries in the index with a matching prefix, it might prevent unnecessary iterations of the IN operator. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | multikey-opt-idea |
| Files: | files | file ages | folders |
| SHA3-256: |
92f0fe155d5546fc7f4a443d0630613d |
| User & Date: | drh 2018-05-30 00:54:23 |
Context
|
2018-06-04
| ||
| 13:27 | Merge recent changes from trunk. check-in: f8df2a8e user: drh tags: multikey-opt-idea | |
|
2018-05-30
| ||
| 00:54 | Demonstration code on a possible technique for optimizing the use of IN operator on columns to the right of multicolumn indexes. If the OP_Noop generated where were really a new opcode that checked to see if there existed any entries in the index with a matching prefix, it might prevent unnecessary iterations of the IN operator. check-in: 92f0fe15 user: drh tags: multikey-opt-idea | |
|
2018-05-29
| ||
| 19:12 | Increase the number of database handles opened by test script oserror.test to provoke an "out of file-descriptors" error to 20000 (from 2000). check-in: 3b00f734 user: dan tags: trunk | |
Changes
Changes to src/where.c.
5079 5080 5081 5082 5083 5084 5085 5086 5087 5088 5089 5090 5091 5092 |
if( pLoop->wsFlags & WHERE_IN_ABLE && pLevel->u.in.nIn>0 ){
struct InLoop *pIn;
int j;
sqlite3VdbeResolveLabel(v, pLevel->addrNxt);
for(j=pLevel->u.in.nIn, pIn=&pLevel->u.in.aInLoop[j-1]; j>0; j--, pIn--){
sqlite3VdbeJumpHere(v, pIn->addrInTop+1);
if( pIn->eEndLoopOp!=OP_Noop ){
sqlite3VdbeAddOp2(v, pIn->eEndLoopOp, pIn->iCur, pIn->addrInTop);
VdbeCoverage(v);
VdbeCoverageIf(v, pIn->eEndLoopOp==OP_PrevIfOpen);
VdbeCoverageIf(v, pIn->eEndLoopOp==OP_NextIfOpen);
}
sqlite3VdbeJumpHere(v, pIn->addrInTop-1);
}
|
> > > > |
5079 5080 5081 5082 5083 5084 5085 5086 5087 5088 5089 5090 5091 5092 5093 5094 5095 5096 |
if( pLoop->wsFlags & WHERE_IN_ABLE && pLevel->u.in.nIn>0 ){
struct InLoop *pIn;
int j;
sqlite3VdbeResolveLabel(v, pLevel->addrNxt);
for(j=pLevel->u.in.nIn, pIn=&pLevel->u.in.aInLoop[j-1]; j>0; j--, pIn--){
sqlite3VdbeJumpHere(v, pIn->addrInTop+1);
if( pIn->eEndLoopOp!=OP_Noop ){
if( pIn->nPrefix ){
sqlite3VdbeAddOp3(v, OP_Noop, pLevel->iIdxCur,
pIn->iBase, pIn->nPrefix);
}
sqlite3VdbeAddOp2(v, pIn->eEndLoopOp, pIn->iCur, pIn->addrInTop);
VdbeCoverage(v);
VdbeCoverageIf(v, pIn->eEndLoopOp==OP_PrevIfOpen);
VdbeCoverageIf(v, pIn->eEndLoopOp==OP_NextIfOpen);
}
sqlite3VdbeJumpHere(v, pIn->addrInTop-1);
}
|
Changes to src/whereInt.h.
78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
int p1, p2; /* Operands of the opcode used to ends the loop */
union { /* Information that depends on pWLoop->wsFlags */
struct {
int nIn; /* Number of entries in aInLoop[] */
struct InLoop {
int iCur; /* The VDBE cursor used by this IN operator */
int addrInTop; /* Top of the IN loop */
u8 eEndLoopOp; /* IN Loop terminator. OP_Next or OP_Prev */
} *aInLoop; /* Information about each nested IN operator */
} in; /* Used when pWLoop->wsFlags&WHERE_IN_ABLE */
Index *pCovidx; /* Possible covering index for WHERE_MULTI_OR */
} u;
struct WhereLoop *pWLoop; /* The selected WhereLoop object */
Bitmask notReady; /* FROM entries not usable at this level */
|
> > |
78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
int p1, p2; /* Operands of the opcode used to ends the loop */
union { /* Information that depends on pWLoop->wsFlags */
struct {
int nIn; /* Number of entries in aInLoop[] */
struct InLoop {
int iCur; /* The VDBE cursor used by this IN operator */
int addrInTop; /* Top of the IN loop */
int iBase; /* Base register of multi-key index record */
int nPrefix; /* Number of prior entires in the key */
u8 eEndLoopOp; /* IN Loop terminator. OP_Next or OP_Prev */
} *aInLoop; /* Information about each nested IN operator */
} in; /* Used when pWLoop->wsFlags&WHERE_IN_ABLE */
Index *pCovidx; /* Possible covering index for WHERE_MULTI_OR */
} u;
struct WhereLoop *pWLoop; /* The selected WhereLoop object */
Bitmask notReady; /* FROM entries not usable at this level */
|
Changes to src/wherecode.c.
588 589 590 591 592 593 594 595 596 597 598 599 600 601 |
int iCol = aiMap ? aiMap[iMap++] : 0;
pIn->addrInTop = sqlite3VdbeAddOp3(v,OP_Column,iTab, iCol, iOut);
}
sqlite3VdbeAddOp1(v, OP_IsNull, iOut); VdbeCoverage(v);
if( i==iEq ){
pIn->iCur = iTab;
pIn->eEndLoopOp = bRev ? OP_PrevIfOpen : OP_NextIfOpen;
}else{
pIn->eEndLoopOp = OP_Noop;
}
pIn++;
}
}
}else{
|
> > |
588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 |
int iCol = aiMap ? aiMap[iMap++] : 0;
pIn->addrInTop = sqlite3VdbeAddOp3(v,OP_Column,iTab, iCol, iOut);
}
sqlite3VdbeAddOp1(v, OP_IsNull, iOut); VdbeCoverage(v);
if( i==iEq ){
pIn->iCur = iTab;
pIn->eEndLoopOp = bRev ? OP_PrevIfOpen : OP_NextIfOpen;
pIn->iBase = iReg - i;
pIn->nPrefix = i;
}else{
pIn->eEndLoopOp = OP_Noop;
}
pIn++;
}
}
}else{
|