Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a couple of unreachable branches. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | skip-ahead-distinct |
| Files: | files | file ages | folders |
| SHA3-256: |
1aa0ea8db7580aff8bed2f78117ed500 |
| User & Date: | drh 2017-04-14 00:45:51 |
Context
|
2017-04-14
| ||
| 22:41 | Fix to the decision logic for when to use the skip-ahead-distinct optimization. Closed-Leaf check-in: e50fd489 user: drh tags: skip-ahead-distinct | |
| 17:30 | When doing a DISTINCT query using an index, try to use the index to skip ahead to the next distinct element, rather than doing a full scan of the index. (This is the "skip-ahead-distinct" optimization.) check-in: f489b5bb user: drh tags: trunk | |
| 00:45 | Fix a couple of unreachable branches. check-in: 1aa0ea8d user: drh tags: skip-ahead-distinct | |
|
2017-04-13
| ||
| 21:29 | Fix the skip-ahead-distinct optimization so that it works with indexes that have repeated columns with different collating sequences. check-in: ce1e2b88 user: drh tags: skip-ahead-distinct | |
Changes
Changes to src/sqliteInt.h.
1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 |
#define SQLITE_DistinctOpt 0x0020 /* DISTINCT using indexes */
#define SQLITE_CoverIdxScan 0x0040 /* Covering index scans */
#define SQLITE_OrderByIdxJoin 0x0080 /* ORDER BY of joins via index */
#define SQLITE_SubqCoroutine 0x0100 /* Evaluate subqueries as coroutines */
#define SQLITE_Transitive 0x0200 /* Transitive constraints */
#define SQLITE_OmitNoopJoin 0x0400 /* Omit unused tables in joins */
#define SQLITE_Stat34 0x0800 /* Use STAT3 or STAT4 data */
#define SQLITE_SkipAhead 0x1000 /* Skip ahead on DISTINCT */
#define SQLITE_CursorHints 0x2000 /* Add OP_CursorHint opcodes */
#define SQLITE_AllOpts 0xffff /* All optimizations */
/*
** Macros for testing whether or not optimizations are enabled or disabled.
*/
#define OptimizationDisabled(db, mask) (((db)->dbOptFlags&(mask))!=0)
|
< |
1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 |
#define SQLITE_DistinctOpt 0x0020 /* DISTINCT using indexes */ #define SQLITE_CoverIdxScan 0x0040 /* Covering index scans */ #define SQLITE_OrderByIdxJoin 0x0080 /* ORDER BY of joins via index */ #define SQLITE_SubqCoroutine 0x0100 /* Evaluate subqueries as coroutines */ #define SQLITE_Transitive 0x0200 /* Transitive constraints */ #define SQLITE_OmitNoopJoin 0x0400 /* Omit unused tables in joins */ #define SQLITE_Stat34 0x0800 /* Use STAT3 or STAT4 data */ #define SQLITE_CursorHints 0x2000 /* Add OP_CursorHint opcodes */ #define SQLITE_AllOpts 0xffff /* All optimizations */ /* ** Macros for testing whether or not optimizations are enabled or disabled. */ #define OptimizationDisabled(db, mask) (((db)->dbOptFlags&(mask))!=0) |
Changes to src/where.c.
3675
3676
3677
3678
3679
3680
3681
3682
3683
3684
3685
3686
3687
3688
3689
....
4853
4854
4855
4856
4857
4858
4859
4860
4861
4862
4863
4864
4865
4866
4867
|
}
}
if( iColumn>=0 ){
pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
if( !pColl ) pColl = db->pDfltColl;
if( sqlite3StrICmp(pColl->zName, pIndex->azColl[j])!=0 ) continue;
}
if( pLoop->u.btree.nIdxCol<=j ) pLoop->u.btree.nIdxCol = j+1;
isMatch = 1;
break;
}
if( isMatch && (wctrlFlags & WHERE_GROUPBY)==0 ){
/* Make sure the sort order is compatible in an ORDER BY clause.
** Sort order is irrelevant for a GROUP BY clause. */
if( revSet ){
................................................................................
if( pLevel->op!=OP_Noop ){
#ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT
int addrSeek = 0;
Index *pIdx;
int n;
if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED
&& (pLoop->wsFlags & WHERE_INDEXED)!=0
&& OptimizationEnabled(db, SQLITE_SkipAhead)
&& (pIdx = pLoop->u.btree.pIndex)->hasStat1
&& pIdx->aiRowLogEst[(n = pLoop->u.btree.nIdxCol)-1]>=36
){
int r1 = pParse->nMem+1;
int j, op;
for(j=0; j<n; j++){
sqlite3VdbeAddOp3(v, OP_Column, pLevel->iIdxCur, j, r1+j);
|
|
<
|
3675
3676
3677
3678
3679
3680
3681
3682
3683
3684
3685
3686
3687
3688
3689
....
4853
4854
4855
4856
4857
4858
4859
4860
4861
4862
4863
4864
4865
4866
|
}
}
if( iColumn>=0 ){
pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
if( !pColl ) pColl = db->pDfltColl;
if( sqlite3StrICmp(pColl->zName, pIndex->azColl[j])!=0 ) continue;
}
pLoop->u.btree.nIdxCol = j+1;
isMatch = 1;
break;
}
if( isMatch && (wctrlFlags & WHERE_GROUPBY)==0 ){
/* Make sure the sort order is compatible in an ORDER BY clause.
** Sort order is irrelevant for a GROUP BY clause. */
if( revSet ){
................................................................................
if( pLevel->op!=OP_Noop ){
#ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT
int addrSeek = 0;
Index *pIdx;
int n;
if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED
&& (pLoop->wsFlags & WHERE_INDEXED)!=0
&& (pIdx = pLoop->u.btree.pIndex)->hasStat1
&& pIdx->aiRowLogEst[(n = pLoop->u.btree.nIdxCol)-1]>=36
){
int r1 = pParse->nMem+1;
int j, op;
for(j=0; j<n; j++){
sqlite3VdbeAddOp3(v, OP_Column, pLevel->iIdxCur, j, r1+j);
|