Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Performance enhancement for single-table queries with many OR-connected WHERE clause terms and multiple indexes with the same left-most columns. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
1461d543ac8a3e4a54405067893146c7 |
| User & Date: | drh 2014-12-04 20:24:50 |
Context
|
2014-12-04
| ||
| 21:54 | Make sure that a DISTINCT query with an ORDER BY works correctly even if it uses a descending index. Fix for ticket [c5ea805691bfc4204b1cb9e]. (check-in: 0d3aef97 user: drh tags: trunk) | |
| 20:24 | Performance enhancement for single-table queries with many OR-connected WHERE clause terms and multiple indexes with the same left-most columns. (check-in: 1461d543 user: drh tags: trunk) | |
| 16:27 | If a table is the right operand of a LEFT JOIN, then any column of that table can be NULL even if that column has a NOT NULL constraint. Fix for ticket [6f2222d550f5b0ee7ed]. (check-in: 6f6fcbe4 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
3587 3588 3589 3590 3591 3592 3593 3594 3595 3596 3597 3598 3599 3600 3601 3602 3603 3604 |
*/
if( pWC->nTerm>1 ){
int iTerm;
for(iTerm=0; iTerm<pWC->nTerm; iTerm++){
Expr *pExpr = pWC->a[iTerm].pExpr;
if( &pWC->a[iTerm] == pTerm ) continue;
if( ExprHasProperty(pExpr, EP_FromJoin) ) continue;
testcase( pWC->a[iTerm].wtFlags & TERM_ORINFO );
testcase( pWC->a[iTerm].wtFlags & TERM_VIRTUAL );
if( pWC->a[iTerm].wtFlags & (TERM_ORINFO|TERM_VIRTUAL) ) continue;
if( (pWC->a[iTerm].eOperator & WO_ALL)==0 ) continue;
pExpr = sqlite3ExprDup(db, pExpr, 0);
pAndExpr = sqlite3ExprAnd(db, pAndExpr, pExpr);
}
if( pAndExpr ){
pAndExpr = sqlite3PExpr(pParse, TK_AND, 0, pAndExpr, 0);
}
}
|
< | < > |
3587 3588 3589 3590 3591 3592 3593 3594 3595 3596 3597 3598 3599 3600 3601 3602 3603 |
*/
if( pWC->nTerm>1 ){
int iTerm;
for(iTerm=0; iTerm<pWC->nTerm; iTerm++){
Expr *pExpr = pWC->a[iTerm].pExpr;
if( &pWC->a[iTerm] == pTerm ) continue;
if( ExprHasProperty(pExpr, EP_FromJoin) ) continue;
if( (pWC->a[iTerm].wtFlags & TERM_VIRTUAL)!=0 ) continue;
if( (pWC->a[iTerm].eOperator & WO_ALL)==0 ) continue;
testcase( pWC->a[iTerm].wtFlags & TERM_ORINFO );
pExpr = sqlite3ExprDup(db, pExpr, 0);
pAndExpr = sqlite3ExprAnd(db, pAndExpr, pExpr);
}
if( pAndExpr ){
pAndExpr = sqlite3PExpr(pParse, TK_AND, 0, pAndExpr, 0);
}
}
|
Changes to test/whereJ.test.
634 635 636 637 638 639 640 641 642 643 |
WHERE
cx.code = '2990'
AND cx.type=2
AND px.cx_id = cx.cx_id
AND px.px_tid = 0
AND px.le_id = le.le_id;
} {/.*SCAN TABLE cx.*SEARCH TABLE px.*SEARCH TABLE le.*/}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 |
WHERE
cx.code = '2990'
AND cx.type=2
AND px.cx_id = cx.cx_id
AND px.px_tid = 0
AND px.le_id = le.le_id;
} {/.*SCAN TABLE cx.*SEARCH TABLE px.*SEARCH TABLE le.*/}
# The following test is derived from a performance problem reported from
# the field. Notice the multiple indexes with the same initial tables,
# and the unusual WHERE clause terms.
#
do_test 5.1 {
set res [db eval {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a,b,c,d,e,f,g,h);
CREATE INDEX t1abc ON t1(a,b,c);
CREATE INDEX t1abe ON t1(a,b,e);
CREATE INDEX t1abf ON t1(a,b,f);
ANALYZE;
DROP TABLE IF EXISTS sqlite_stat4;
DROP TABLE IF EXISTS sqlite_stat3;
DELETE FROM sqlite_stat1;
INSERT INTO sqlite_stat1(tbl,idx,stat)
VALUES('t1','t1abc','2000000 8000 1600 800'),
('t1','t1abe','2000000 8000 1600 150'),
('t1','t1abf','2000000 8000 1600 150');
ANALYZE sqlite_master;
EXPLAIN QUERY PLAN
SELECT * FROM t1
WHERE (a=1 OR a=2)
AND (b=3 OR b=4)
AND (d>=5 AND d<=5)
AND ((e>=7 AND e<=7) OR (f>=8 AND f<=8))
AND g>0;
}]
} {~/ANY/}
do_test 5.2 {set res} {/USING INDEX t1abe/}
do_test 5.3 {set res} {/USING INDEX t1abf/}
finish_test
|