Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Enhance the query planner so that it is able to use partial indexes that use AND-connected terms in the WHERE clause. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
065765902d2774d7432b9c00ea2efed2 |
| User & Date: | drh 2015-08-07 20:57:00 |
Context
|
2015-08-08
| ||
| 15:13 | Update RBU to avoid repreparing a statement immediately after it is prepared. check-in: 1d75a41b user: dan tags: trunk | |
|
2015-08-07
| ||
| 20:57 | Enhance the query planner so that it is able to use partial indexes that use AND-connected terms in the WHERE clause. check-in: 06576590 user: drh tags: trunk | |
| 20:06 | Add a specially formatted comment to shell.c to make it easier for scripts to edit. No code changes. check-in: 6d47b35a user: dan tags: trunk | |
Changes
Changes to src/where.c.
2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 2429 2430 2431 2432 |
/* Check to see if a partial index with pPartIndexWhere can be used
** in the current query. Return true if it can be and false if not.
*/
static int whereUsablePartialIndex(int iTab, WhereClause *pWC, Expr *pWhere){
int i;
WhereTerm *pTerm;
for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
Expr *pExpr = pTerm->pExpr;
if( sqlite3ExprImpliesExpr(pExpr, pWhere, iTab)
&& (!ExprHasProperty(pExpr, EP_FromJoin) || pExpr->iRightJoinTable==iTab)
){
return 1;
}
|
> > > > |
2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 2429 2430 2431 2432 2433 2434 2435 2436 |
/* Check to see if a partial index with pPartIndexWhere can be used
** in the current query. Return true if it can be and false if not.
*/
static int whereUsablePartialIndex(int iTab, WhereClause *pWC, Expr *pWhere){
int i;
WhereTerm *pTerm;
while( pWhere->op==TK_AND ){
if( !whereUsablePartialIndex(iTab,pWC,pWhere->pLeft) ) return 0;
pWhere = pWhere->pRight;
}
for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
Expr *pExpr = pTerm->pExpr;
if( sqlite3ExprImpliesExpr(pExpr, pWhere, iTab)
&& (!ExprHasProperty(pExpr, EP_FromJoin) || pExpr->iRightJoinTable==iTab)
){
return 1;
}
|
Changes to test/index6.test.
341 342 343 344 345 346 347 348 349 |
CREATE TABLE t9(a int, b int, c int, PRIMARY KEY(a)) WITHOUT ROWID;
CREATE INDEX t9ca ON t9(c,a) WHERE a in (10,12,20);
INSERT INTO t9 VALUES(1,1,9),(10,2,35),(11,15,82),(20,19,5);
UPDATE t9 SET b=c WHERE a in (10,12,20);
SELECT a,b,c,'|' FROM t9 ORDER BY a;
} {1 1 9 | 10 35 35 | 11 15 82 | 20 5 5 |}
finish_test
|
> | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 |
CREATE TABLE t9(a int, b int, c int, PRIMARY KEY(a)) WITHOUT ROWID;
CREATE INDEX t9ca ON t9(c,a) WHERE a in (10,12,20);
INSERT INTO t9 VALUES(1,1,9),(10,2,35),(11,15,82),(20,19,5);
UPDATE t9 SET b=c WHERE a in (10,12,20);
SELECT a,b,c,'|' FROM t9 ORDER BY a;
} {1 1 9 | 10 35 35 | 11 15 82 | 20 5 5 |}
# AND-connected terms in the WHERE clause of a partial index
#
do_execsql_test index6-10.1 {
CREATE TABLE t10(a,b,c,d,e INTEGER PRIMARY KEY);
INSERT INTO t10 VALUES
(1,2,3,4,5),
(2,3,4,5,6),
(3,4,5,6,7),
(1,2,3,8,9);
CREATE INDEX t10x ON t10(d) WHERE a=1 AND b=2 AND c=3;
SELECT e FROM t10 WHERE a=1 AND b=2 AND c=3 ORDER BY d;
} {5 9}
do_execsql_test index6-10.1eqp {
EXPLAIN QUERY PLAN
SELECT e FROM t10 WHERE a=1 AND b=2 AND c=3 ORDER BY d;
} {/USING INDEX t10x/}
do_execsql_test index6-10.2 {
SELECT e FROM t10 WHERE c=3 AND 2=b AND a=1 ORDER BY d DESC;
} {9 5}
do_execsql_test index6-10.2eqp {
EXPLAIN QUERY PLAN
SELECT e FROM t10 WHERE c=3 AND 2=b AND a=1 ORDER BY d DESC;
} {/USING INDEX t10x/}
do_execsql_test index6-10.3 {
SELECT e FROM t10 WHERE a=1 AND b=2 ORDER BY d DESC;
} {9 5}
do_execsql_test index6-10.3eqp {
EXPLAIN QUERY PLAN
SELECT e FROM t10 WHERE a=1 AND b=2 ORDER BY d DESC;
} {~/USING INDEX t10x/}
finish_test
|