Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Enhance the query planner so that it is able to use indexed expressions to help fulfill an ORDER BY clause. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
668fc1ebaf426f9eed3ed7865e41f102 |
User & Date: | drh 2015-09-24 18:47:59.761 |
Context
2015-09-25
| ||
01:09 | Fix a problem computing affinities for indexes during skip-scan code generation when SQLITE_ENABLE_STAT4 is on. Bug introduced by check-in [1ee089a72d789002]. (check-in: 1350dd42d0 user: drh tags: trunk) | |
2015-09-24
| ||
18:47 | Enhance the query planner so that it is able to use indexed expressions to help fulfill an ORDER BY clause. (check-in: 668fc1ebaf user: drh tags: trunk) | |
17:38 | Fix over-length source code lines in where.c. No logic changes. (check-in: 1c8c5380a8 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
2417 2418 2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 2429 | */ static int indexMightHelpWithOrderBy( WhereLoopBuilder *pBuilder, Index *pIndex, int iCursor ){ ExprList *pOB; int ii, jj; if( pIndex->bUnordered ) return 0; if( (pOB = pBuilder->pWInfo->pOrderBy)==0 ) return 0; for(ii=0; ii<pOB->nExpr; ii++){ Expr *pExpr = sqlite3ExprSkipCollate(pOB->a[ii].pExpr); | > < | > > > > > > > | 2417 2418 2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 2429 2430 2431 2432 2433 2434 2435 2436 2437 2438 2439 2440 2441 2442 2443 2444 2445 2446 2447 2448 | */ static int indexMightHelpWithOrderBy( WhereLoopBuilder *pBuilder, Index *pIndex, int iCursor ){ ExprList *pOB; ExprList *aColExpr; int ii, jj; if( pIndex->bUnordered ) return 0; if( (pOB = pBuilder->pWInfo->pOrderBy)==0 ) return 0; for(ii=0; ii<pOB->nExpr; ii++){ Expr *pExpr = sqlite3ExprSkipCollate(pOB->a[ii].pExpr); if( pExpr->op==TK_COLUMN && pExpr->iTable==iCursor ){ if( pExpr->iColumn<0 ) return 1; for(jj=0; jj<pIndex->nKeyCol; jj++){ if( pExpr->iColumn==pIndex->aiColumn[jj] ) return 1; } }else if( (aColExpr = pIndex->aColExpr)!=0 ){ for(jj=0; jj<pIndex->nKeyCol; jj++){ if( pIndex->aiColumn[jj]!=(-2) ) continue; if( sqlite3ExprCompare(pExpr,aColExpr->a[jj].pExpr,iCursor)==0 ){ return 1; } } } } return 0; } /* |
︙ | ︙ | |||
3260 3261 3262 3263 3264 3265 3266 | isMatch = 0; for(i=0; bOnce && i<nOrderBy; i++){ if( MASKBIT(i) & obSat ) continue; pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr); testcase( wctrlFlags & WHERE_GROUPBY ); testcase( wctrlFlags & WHERE_DISTINCTBY ); if( (wctrlFlags & (WHERE_GROUPBY|WHERE_DISTINCTBY))==0 ) bOnce = 0; | > | | | > > > > > | 3267 3268 3269 3270 3271 3272 3273 3274 3275 3276 3277 3278 3279 3280 3281 3282 3283 3284 3285 3286 3287 3288 3289 | isMatch = 0; for(i=0; bOnce && i<nOrderBy; i++){ if( MASKBIT(i) & obSat ) continue; pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr); testcase( wctrlFlags & WHERE_GROUPBY ); testcase( wctrlFlags & WHERE_DISTINCTBY ); if( (wctrlFlags & (WHERE_GROUPBY|WHERE_DISTINCTBY))==0 ) bOnce = 0; if( iColumn>=(-1) ){ if( pOBExpr->op!=TK_COLUMN ) continue; if( pOBExpr->iTable!=iCur ) continue; if( pOBExpr->iColumn!=iColumn ) continue; }else{ if( sqlite3ExprCompare(pOBExpr,pIndex->aColExpr->a[j].pExpr,iCur) ){ continue; } } 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; } isMatch = 1; break; |
︙ | ︙ |
Changes to test/indexexpr1.test.
︙ | ︙ | |||
84 85 86 87 88 89 90 91 92 93 94 95 96 97 | } {1 1 1} do_execsql_test indexexpr1-160eqp { EXPLAIN QUERY PLAN SELECT rowid, b, c FROM t1 WHERE substr(a,27,3)=='ord' AND d>=29; } {/USING INDEX t1a2/} do_execsql_test indexexpr1-200 { DROP TABLE t1; CREATE TABLE t1(id ANY PRIMARY KEY, a,b,c) WITHOUT ROWID; INSERT INTO t1(id,a,b,c) VALUES(1,'In_the_beginning_was_the_Word',1,1), (2,'and_the_Word_was_with_God',1,2), | > > > > > > > > > > > > > > > > > | 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 | } {1 1 1} do_execsql_test indexexpr1-160eqp { EXPLAIN QUERY PLAN SELECT rowid, b, c FROM t1 WHERE substr(a,27,3)=='ord' AND d>=29; } {/USING INDEX t1a2/} # ORDER BY using an indexed expression # do_execsql_test indexexpr1-170 { CREATE INDEX t1alen ON t1(length(a)); SELECT length(a) FROM t1 ORDER BY length(a); } {20 25 27 29 38 52} do_execsql_test indexexpr1-170eqp { EXPLAIN QUERY PLAN SELECT length(a) FROM t1 ORDER BY length(a); } {/SCAN TABLE t1 USING INDEX t1alen/} do_execsql_test indexexpr1-171 { SELECT length(a) FROM t1 ORDER BY length(a) DESC; } {52 38 29 27 25 20} do_execsql_test indexexpr1-171eqp { EXPLAIN QUERY PLAN SELECT length(a) FROM t1 ORDER BY length(a) DESC; } {/SCAN TABLE t1 USING INDEX t1alen/} do_execsql_test indexexpr1-200 { DROP TABLE t1; CREATE TABLE t1(id ANY PRIMARY KEY, a,b,c) WITHOUT ROWID; INSERT INTO t1(id,a,b,c) VALUES(1,'In_the_beginning_was_the_Word',1,1), (2,'and_the_Word_was_with_God',1,2), |
︙ | ︙ | |||
250 251 252 253 254 255 256 257 258 | CREATE INDEX t72yz ON t72(y+z); INSERT INTO t71(a,b,c) VALUES(1,11,2),(2,7,15),(3,5,4); INSERT INTO t72(x,y,z) VALUES(1,10,3),(2,8,14),(3,9,9); SELECT a, x, '|' FROM t71, t72 WHERE b+c=y+z ORDER BY +a, +x; } {1 1 | 2 2 |} finish_test | > > > > > > > > > > > > > > > > > > > > | 267 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 | CREATE INDEX t72yz ON t72(y+z); INSERT INTO t71(a,b,c) VALUES(1,11,2),(2,7,15),(3,5,4); INSERT INTO t72(x,y,z) VALUES(1,10,3),(2,8,14),(3,9,9); SELECT a, x, '|' FROM t71, t72 WHERE b+c=y+z ORDER BY +a, +x; } {1 1 | 2 2 |} # Collating sequences on indexes of expressions # do_execsql_test indexexpr1-800 { DROP TABLE IF EXISTS t8; CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT); CREATE UNIQUE INDEX t8bx ON t8(substr(b,2,4) COLLATE nocase); INSERT INTO t8(a,b) VALUES(1,'Alice'),(2,'Bartholemew'),(3,'Cynthia'); SELECT * FROM t8 WHERE substr(b,2,4)='ARTH' COLLATE nocase; } {2 Bartholemew} do_catchsql_test indexexpr1-810 { INSERT INTO t8(a,b) VALUES(4,'BARTHMERE'); } {1 {UNIQUE constraint failed: index 't8bx'}} do_catchsql_test indexexpr1-820 { DROP INDEX t8bx; CREATE UNIQUE INDEX t8bx ON t8(substr(b,2,4) COLLATE rtrim); INSERT INTO t8(a,b) VALUES(4,'BARTHMERE'); } {0 {}} finish_test |