Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Remove a redundant test case (probably a copy/paste error). Add an assert() to where.c to ensure that automatic indexes do not have there output row counts adjusted downward by supplementary constraints. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
eea47933493c85a85247ad383bd148b0 |
| User & Date: | drh 2014-11-22 12:22:13 |
Context
|
2014-11-22
| ||
| 18:50 | Deploy heuristics (well-commented) to better estimate how much unindexed terms in the WHERE clause filter the number of output rows from a single table. check-in: 22165994 user: drh tags: trunk | |
| 16:14 | Unintentional edits mistakenly checked in. Was: Remove an obsolete and incorrect comment from the whereLoopOutputAdjust() routine in the query planner. No changes to working code. Closed-Leaf check-in: e1f893c6 user: drh tags: mistake | |
| 12:22 | Remove a redundant test case (probably a copy/paste error). Add an assert() to where.c to ensure that automatic indexes do not have there output row counts adjusted downward by supplementary constraints. check-in: eea47933 user: drh tags: trunk | |
|
2014-11-20
| ||
| 23:21 | Fix a typo in a requirements mark on the abs() SQL function. check-in: b1e6c02f user: drh tags: trunk | |
Changes
Changes to src/where.c.
4302 4303 4304 4305 4306 4307 4308 4309 4310 4311 4312 4313 4314 4315 |
LogEst nRow /* Number of rows in the entire table */
){
WhereTerm *pTerm, *pX;
Bitmask notAllowed = ~(pLoop->prereq|pLoop->maskSelf);
int i, j;
int nEq = 0; /* Number of = constraints not within likely()/unlikely() */
for(i=pWC->nTerm, pTerm=pWC->a; i>0; i--, pTerm++){
if( (pTerm->wtFlags & TERM_VIRTUAL)!=0 ) break;
if( (pTerm->prereqAll & pLoop->maskSelf)==0 ) continue;
if( (pTerm->prereqAll & notAllowed)!=0 ) continue;
for(j=pLoop->nLTerm-1; j>=0; j--){
pX = pLoop->aLTerm[j];
if( pX==0 ) continue;
|
> |
4302 4303 4304 4305 4306 4307 4308 4309 4310 4311 4312 4313 4314 4315 4316 |
LogEst nRow /* Number of rows in the entire table */
){
WhereTerm *pTerm, *pX;
Bitmask notAllowed = ~(pLoop->prereq|pLoop->maskSelf);
int i, j;
int nEq = 0; /* Number of = constraints not within likely()/unlikely() */
assert( (pLoop->wsFlags & WHERE_AUTO_INDEX)==0 );
for(i=pWC->nTerm, pTerm=pWC->a; i>0; i--, pTerm++){
if( (pTerm->wtFlags & TERM_VIRTUAL)!=0 ) break;
if( (pTerm->prereqAll & pLoop->maskSelf)==0 ) continue;
if( (pTerm->prereqAll & notAllowed)!=0 ) continue;
for(j=pLoop->nLTerm-1; j>=0; j--){
pX = pLoop->aLTerm[j];
if( pX==0 ) continue;
|
Changes to test/autoindex2.test.
220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 |
ORDER BY t1.ptime desc LIMIT 500;
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1x1 (ptime>?)} 0 1 1 {SEARCH TABLE t2 USING INDEX t2x0 (did=?)} 0 2 2 {SEARCH TABLE t3 USING INDEX t3x0 (uid=?)}}
#
# ^^^--- Before being fixed, the above was using an automatic covering
# on t3 and reordering the tables so that t3 was in the outer loop and
# implementing the ORDER BY clause using a B-Tree.
do_execsql_test autoindex2-120 {
EXPLAIN QUERY PLAN
SELECT
t1_id,
t1.did,
param2,
param3,
t1.ptime,
t1.trange,
t1.exmass,
t1.mass,
t1.vstatus,
type,
subtype,
t1.deviation,
t1.formula,
dparam1,
reserve1,
reserve2,
param4,
t1.last_operation,
t1.admin_uuid,
t1.previous_value,
t1.job_id,
client_did,
t1.last_t1,
t1.data_t1,
t1.previous_date,
param5,
param6,
mgr_uuid
FROM
t3,
t2,
t1
WHERE
t1.ptime > 1393520400
AND param3<>9001
AND t3.flg7 = 1
AND t1.did = t2.did
AND t2.uid = t3.uid
ORDER BY t1.ptime desc LIMIT 500;
} {0 0 2 {SEARCH TABLE t1 USING INDEX t1x1 (ptime>?)} 0 1 1 {SEARCH TABLE t2 USING INDEX t2x0 (did=?)} 0 2 0 {SEARCH TABLE t3 USING INDEX t3x0 (uid=?)}}
finish_test
|
< < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < |
220 221 222 223 224 225 226 227 |
ORDER BY t1.ptime desc LIMIT 500;
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1x1 (ptime>?)} 0 1 1 {SEARCH TABLE t2 USING INDEX t2x0 (did=?)} 0 2 2 {SEARCH TABLE t3 USING INDEX t3x0 (uid=?)}}
#
# ^^^--- Before being fixed, the above was using an automatic covering
# on t3 and reordering the tables so that t3 was in the outer loop and
# implementing the ORDER BY clause using a B-Tree.
finish_test
|