Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch noskipscan-token Excluding Merge-Ins
This is equivalent to a diff from 55e453aa to 00fe0950
|
2014-11-18
| ||
| 21:54 | Adding the "noskipscan" token to an sqlite_stat1.stat field prevents an index for being used with the skip-scan algorithm. (check-in: 4461bf04 user: drh tags: trunk) | |
|
2014-11-17
| ||
| 19:42 | Avoid calling sqlite3BtreeKeysize() on a b-tree cursor in SKIPNEXT or SKIPPREV state. (check-in: 54e7d3fc user: dan tags: trunk) | |
|
2014-11-15
| ||
| 19:08 | Adding the "noskipscan" token to an sqlite_stat1.stat field prevents an index for being used with the skip-scan algorithm. (Closed-Leaf check-in: 00fe0950 user: drh tags: noskipscan-token) | |
|
2014-11-14
| ||
| 19:34 | Consider using an automatic-index for a scan even if there exists a possible skip-scan that uses one or more "=" operators. (Closed-Leaf check-in: 93642a65 user: dan tags: experimental-autoindex-fix) | |
| 15:42 | Do not automatically remove the DISTINCT keyword from "a IN (SELECT DISTINCT ...)" expressions. Fix for [db87229497]. (check-in: 98457a57 user: drh tags: branch-3.8.7) | |
| 15:28 | Do not automatically remove the DISTINCT keyword from "a IN (SELECT DISTINCT ...)" expressions. Fix for [db87229497]. (check-in: 55e453aa user: dan tags: trunk) | |
|
2014-11-13
| ||
| 14:30 | Modify the documentation for sqlite3_backup_init() to indicate that it will fail if there is already a read or read-write transaction open on the destination database. (check-in: ef03a203 user: dan tags: trunk) | |
Changes to src/analyze.c.
| ︙ | ︙ | |||
1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 |
if( pIndex )
#endif
while( z[0] ){
if( sqlite3_strglob("unordered*", z)==0 ){
pIndex->bUnordered = 1;
}else if( sqlite3_strglob("sz=[0-9]*", z)==0 ){
pIndex->szIdxRow = sqlite3LogEst(sqlite3Atoi(z+3));
}
#ifdef SQLITE_ENABLE_COSTMULT
else if( sqlite3_strglob("costmult=[0-9]*",z)==0 ){
pIndex->pTable->costMult = sqlite3LogEst(sqlite3Atoi(z+9));
}
#endif
while( z[0]!=0 && z[0]!=' ' ) z++;
| > > | 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 |
if( pIndex )
#endif
while( z[0] ){
if( sqlite3_strglob("unordered*", z)==0 ){
pIndex->bUnordered = 1;
}else if( sqlite3_strglob("sz=[0-9]*", z)==0 ){
pIndex->szIdxRow = sqlite3LogEst(sqlite3Atoi(z+3));
}else if( sqlite3_strglob("noskipscan*", z)==0 ){
pIndex->noSkipScan = 1;
}
#ifdef SQLITE_ENABLE_COSTMULT
else if( sqlite3_strglob("costmult=[0-9]*",z)==0 ){
pIndex->pTable->costMult = sqlite3LogEst(sqlite3Atoi(z+9));
}
#endif
while( z[0]!=0 && z[0]!=' ' ) z++;
|
| ︙ | ︙ |
Changes to src/sqliteInt.h.
| ︙ | ︙ | |||
1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 | u16 nColumn; /* Number of columns stored in the index */ u8 onError; /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */ unsigned idxType:2; /* 1==UNIQUE, 2==PRIMARY KEY, 0==CREATE INDEX */ unsigned bUnordered:1; /* Use this index for == or IN queries only */ unsigned uniqNotNull:1; /* True if UNIQUE and NOT NULL for all columns */ unsigned isResized:1; /* True if resizeIndexObject() has been called */ unsigned isCovering:1; /* True if this is a covering index */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 int nSample; /* Number of elements in aSample[] */ int nSampleCol; /* Size of IndexSample.anEq[] and so on */ tRowcnt *aAvgEq; /* Average nEq values for keys not in aSample */ IndexSample *aSample; /* Samples of the left-most key */ tRowcnt *aiRowEst; /* Non-logarithmic stat1 data for this index */ tRowcnt nRowEst0; /* Non-logarithmic number of rows in the index */ | > | 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 | u16 nColumn; /* Number of columns stored in the index */ u8 onError; /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */ unsigned idxType:2; /* 1==UNIQUE, 2==PRIMARY KEY, 0==CREATE INDEX */ unsigned bUnordered:1; /* Use this index for == or IN queries only */ unsigned uniqNotNull:1; /* True if UNIQUE and NOT NULL for all columns */ unsigned isResized:1; /* True if resizeIndexObject() has been called */ unsigned isCovering:1; /* True if this is a covering index */ unsigned noSkipScan:1; /* Do not try to use skip-scan if true */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 int nSample; /* Number of elements in aSample[] */ int nSampleCol; /* Size of IndexSample.anEq[] and so on */ tRowcnt *aAvgEq; /* Average nEq values for keys not in aSample */ IndexSample *aSample; /* Samples of the left-most key */ tRowcnt *aiRowEst; /* Non-logarithmic stat1 data for this index */ tRowcnt nRowEst0; /* Non-logarithmic number of rows in the index */ |
| ︙ | ︙ |
Changes to src/where.c.
| ︙ | ︙ | |||
4583 4584 4585 4586 4587 4588 4589 4590 4591 4592 4593 4594 4595 4596 |
** contains fewer than 2^17 rows we assume otherwise in other parts of
** the code). And, even if it is not, it should not be too much slower.
** On the other hand, the extra seeks could end up being significantly
** more expensive. */
assert( 42==sqlite3LogEst(18) );
if( saved_nEq==saved_nSkip
&& saved_nEq+1<pProbe->nKeyCol
&& pProbe->aiRowLogEst[saved_nEq+1]>=42 /* TUNING: Minimum for skip-scan */
&& (rc = whereLoopResize(db, pNew, pNew->nLTerm+1))==SQLITE_OK
){
LogEst nIter;
pNew->u.btree.nEq++;
pNew->nSkip++;
pNew->aLTerm[pNew->nLTerm++] = 0;
| > | 4583 4584 4585 4586 4587 4588 4589 4590 4591 4592 4593 4594 4595 4596 4597 |
** contains fewer than 2^17 rows we assume otherwise in other parts of
** the code). And, even if it is not, it should not be too much slower.
** On the other hand, the extra seeks could end up being significantly
** more expensive. */
assert( 42==sqlite3LogEst(18) );
if( saved_nEq==saved_nSkip
&& saved_nEq+1<pProbe->nKeyCol
&& pProbe->noSkipScan==0
&& pProbe->aiRowLogEst[saved_nEq+1]>=42 /* TUNING: Minimum for skip-scan */
&& (rc = whereLoopResize(db, pNew, pNew->nLTerm+1))==SQLITE_OK
){
LogEst nIter;
pNew->u.btree.nEq++;
pNew->nSkip++;
pNew->aLTerm[pNew->nLTerm++] = 0;
|
| ︙ | ︙ |
Changes to test/skipscan1.test.
| ︙ | ︙ | |||
268 269 270 271 272 273 274 275 276 |
} {/ANY.a. AND b=/}
do_execsql_test skipscan1-6.3 {
-- Two distinct values for the skip-scan column again. Skip-scan is not used.
UPDATE sqlite_stat1 SET stat='500000 125000 62500';
ANALYZE sqlite_master;
EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=1;
} {~/ANY/}
finish_test
| > > > > > > > > > > > > > > > > > > > | 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 |
} {/ANY.a. AND b=/}
do_execsql_test skipscan1-6.3 {
-- Two distinct values for the skip-scan column again. Skip-scan is not used.
UPDATE sqlite_stat1 SET stat='500000 125000 62500';
ANALYZE sqlite_master;
EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=1;
} {~/ANY/}
# If the sqlite_stat1 entry includes the "noskipscan" token, then never use
# skipscan with that index.
#
do_execsql_test skipscan1-7.1 {
UPDATE sqlite_stat1 SET stat='500000 125000 1 sz=100';
ANALYZE sqlite_master;
EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=1;
} {/ANY/}
do_execsql_test skipscan1-7.2 {
UPDATE sqlite_stat1 SET stat='500000 125000 1 noskipscan sz=100';
ANALYZE sqlite_master;
EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=1;
} {~/ANY/}
do_execsql_test skipscan1-7.3 {
UPDATE sqlite_stat1 SET stat='500000 125000 1 sz=100 noskipscan';
ANALYZE sqlite_master;
EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=1;
} {~/ANY/}
finish_test
|