Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Minor size and performance improvements to the push-down optimization. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | pushdown-optimization |
| Files: | files | file ages | folders |
| SHA3-256: |
91dfb61a1a25763bb0b5c1e353a9d584 |
| User & Date: | drh 2017-04-29 14:56:55.202 |
Context
|
2017-04-29
| ||
| 15:27 | Evaluate WHERE clause terms that reference only the index before evaluating terms that require the table, and thereby avoid seeking the table row if index terms are false. This is called the "push-down" optimization in the MySQL world, we are told. Do not confuse with WHERE-clause push-down. (check-in: d7bb79ed3a user: drh tags: trunk) | |
| 14:56 | Minor size and performance improvements to the push-down optimization. (Closed-Leaf check-in: 91dfb61a1a user: drh tags: pushdown-optimization) | |
|
2017-04-28
| ||
| 19:59 | Within a loop that uses a non-covering index test, test non-indexed terms that can be tested without seeking the main table cursor before those that cannot. (check-in: afe68f0a80 user: dan tags: pushdown-optimization) | |
Changes
Changes to src/wherecode.c.
| ︙ | ︙ | |||
1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 | struct SrcList_item *pTabItem; /* FROM clause term being coded */ int addrBrk; /* Jump here to break out of the loop */ int addrHalt; /* addrBrk for the outermost loop */ int addrCont; /* Jump here to continue with next cycle */ int iRowidReg = 0; /* Rowid is stored in this register, if not zero */ int iReleaseReg = 0; /* Temp register to free before returning */ Index *pIdx = 0; /* Index used by loop (if any) */ pParse = pWInfo->pParse; v = pParse->pVdbe; pWC = &pWInfo->sWC; db = pParse->db; pLevel = &pWInfo->a[iLevel]; pLoop = pLevel->pWLoop; | > | 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 | struct SrcList_item *pTabItem; /* FROM clause term being coded */ int addrBrk; /* Jump here to break out of the loop */ int addrHalt; /* addrBrk for the outermost loop */ int addrCont; /* Jump here to continue with next cycle */ int iRowidReg = 0; /* Rowid is stored in this register, if not zero */ int iReleaseReg = 0; /* Temp register to free before returning */ Index *pIdx = 0; /* Index used by loop (if any) */ int loopAgain; /* True if constraint generator loop should repeat */ pParse = pWInfo->pParse; v = pParse->pVdbe; pWC = &pWInfo->sWC; db = pParse->db; pLevel = &pWInfo->a[iLevel]; pLoop = pLevel->pWLoop; |
| ︙ | ︙ | |||
2023 2024 2025 2026 2027 2028 2029 | /* Insert code to test every subexpression that can be completely ** computed using the current set of tables. ** ** This loop may run either once (pIdx==0) or twice (pIdx!=0). If ** it is run twice, then the first iteration codes those sub-expressions ** that can be computed using columns from pIdx only (without seeking | | < > > > | | > | 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057 2058 2059 2060 |
/* Insert code to test every subexpression that can be completely
** computed using the current set of tables.
**
** This loop may run either once (pIdx==0) or twice (pIdx!=0). If
** it is run twice, then the first iteration codes those sub-expressions
** that can be computed using columns from pIdx only (without seeking
** the main table cursor).
*/
do{
loopAgain = 0;
for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
Expr *pE;
int skipLikeAddr = 0;
testcase( pTerm->wtFlags & TERM_VIRTUAL );
testcase( pTerm->wtFlags & TERM_CODED );
if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
if( (pTerm->prereqAll & pLevel->notReady)!=0 ){
testcase( pWInfo->untestedTerms==0
&& (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)!=0 );
pWInfo->untestedTerms = 1;
continue;
}
pE = pTerm->pExpr;
assert( pE!=0 );
if( pLevel->iLeftJoin && !ExprHasProperty(pE, EP_FromJoin) ){
continue;
}
if( pIdx && !sqlite3ExprCoveredByIndex(pE, pLevel->iTabCur, pIdx) ){
loopAgain = 1;
continue;
}
if( pTerm->wtFlags & TERM_LIKECOND ){
/* If the TERM_LIKECOND flag is set, that means that the range search
** is sufficient to guarantee that the LIKE operator is true, so we
** can skip the call to the like(A,B) function. But this only works
** for strings. So do not skip the call to the function on the pass
|
| ︙ | ︙ | |||
2064 2065 2066 2067 2068 2069 2070 |
VdbeCoverage(v);
#endif
}
sqlite3ExprIfFalse(pParse, pE, addrCont, SQLITE_JUMPIFNULL);
if( skipLikeAddr ) sqlite3VdbeJumpHere(v, skipLikeAddr);
pTerm->wtFlags |= TERM_CODED;
}
| < | | 2068 2069 2070 2071 2072 2073 2074 2075 2076 2077 2078 2079 2080 2081 2082 2083 |
VdbeCoverage(v);
#endif
}
sqlite3ExprIfFalse(pParse, pE, addrCont, SQLITE_JUMPIFNULL);
if( skipLikeAddr ) sqlite3VdbeJumpHere(v, skipLikeAddr);
pTerm->wtFlags |= TERM_CODED;
}
pIdx = 0;
}while( loopAgain );
/* Insert code to test for implied constraints based on transitivity
** of the "==" operator.
**
** Example: If the WHERE clause contains "t1.a=t2.b" and "t2.b=123"
** and we are coding the t1 loop and the t2 loop has not yet coded,
** then we cannot use the "t1.a=t2.b" constraint, but we can code
|
| ︙ | ︙ |