/ Check-in [1461d543]
Login

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:1461d543ac8a3e4a54405067893146c74576bb4e
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
Hide Diffs Unified Diffs Ignore Whitespace Patch

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