/ Changes On Branch autoindex-planning
Login
☰Home Timeline Forum
Hide Diff Patch Side-by-Side Diff

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Changes In Branch autoindex-planning Excluding Merge-Ins

This is equivalent to a diff from 4003db4a49 to 12ef3a8f3d

2016-01-26
20:19
Performance improvements for fts5, particularly detail=col mode. (check-in: a3d7b8ac53 user: dan tags: trunk)
15:23
Change the automatic index mechanism so that it avoids creating transient indexes on columns that are known to have low cardinality. (Leaf check-in: 12ef3a8f3d user: drh tags: autoindex-planning)
14:48
Fix issues on unix with opening database files via symlinks that are not in the current working directory. And with nested symlinks. (check-in: 4003db4a49 user: dan tags: trunk)
13:56
Ensure that unixFullpathname() always nul-terminates its output buffer, even when returning an error. (Closed-Leaf check-in: 4a4385564d user: dan tags: follow-symlinks)
2016-01-25
23:24
Add the ability to do Windows builds to the amalgamation tarball. (check-in: abd2b357c5 user: drh tags: trunk)

Changes to src/analyze.c.

︙
1525
1526
1527
1528
1529
1530
1531
1532













1533
1534
1535
1536
1537
1538
1539

1525
1526
1527
1528
1529
1530
1531

1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551








-
+
+
+
+
+
+
+
+
+
+
+
+
+








      pIndex->aiRowEst = (tRowcnt*)sqlite3MallocZero(sizeof(tRowcnt) * nCol);
      if( pIndex->aiRowEst==0 ) pInfo->db->mallocFailed = 1;
    }
    aiRowEst = pIndex->aiRowEst;
#endif
    pIndex->bUnordered = 0;
    decodeIntArray((char*)z, nCol, aiRowEst, pIndex->aiRowLogEst, pIndex);
    if( pIndex->pPartIdxWhere==0 ) pTable->nRowLogEst = pIndex->aiRowLogEst[0];
    if( pIndex->pPartIdxWhere==0 ){
      int i, j;
      /* TUNING: Any column that cannot narrow down the number of table rows
      ** to less than 30 should not be considered as a column for use in
      ** an automatic index */
      for(i=0; i<nCol; i++){
        assert( 49==sqlite3LogEst(30) );
        if( aiRowEst[i+1]>49 && (j = pIndex->aiColumn[i])>=0 ){
          pTable->aCol[j].colFlags |= COLFLAG_NOAUTO;
        }
      }
      pTable->nRowLogEst = pIndex->aiRowLogEst[0];
    }
  }else{
    Index fakeIdx;
    fakeIdx.szIdxRow = pTable->szTabRow;
#ifdef SQLITE_ENABLE_COSTMULT
    fakeIdx.pTable = pTable;
#endif
    decodeIntArray((char*)z, 1, 0, &pTable->nRowLogEst, &fakeIdx);
︙

Changes to src/build.c.

︙
3149
3150
3151
3152
3153
3154
3155

3156
3157









3158
3159
3160
3161
3162
3163
3164

3149
3150
3151
3152
3153
3154
3155
3156


3157
3158
3159
3160
3161
3162
3163
3164
3165
3166
3167
3168
3169
3170
3171
3172








+
-
-
+
+
+
+
+
+
+
+
+








      pIndex->aiColumn[i] = XN_EXPR;
      pIndex->uniqNotNull = 0;
    }else{
      j = pCExpr->iColumn;
      assert( j<=0x7fff );
      if( j<0 ){
        j = pTab->iPKey;
      }else{
      }else if( pTab->aCol[j].notNull==0 ){
        pIndex->uniqNotNull = 0;
        if( pTab->aCol[j].notNull==0 ){
          pIndex->uniqNotNull = 0;
        }
        if( i==0 && pPIWhere==0 ){
          /* Exclude the left-most column of every complete index from
          ** consideration as an automatic index term.  Better to use the real
          ** index */
          pTab->aCol[j].colFlags |= COLFLAG_NOAUTO;
        }
      }
      pIndex->aiColumn[i] = (i16)j;
    }
    zColl = 0;
    if( pListItem->pExpr->op==TK_COLLATE ){
      int nColl;
      zColl = pListItem->pExpr->u.zToken;
︙

Changes to src/sqliteInt.h.

︙
1536
1537
1538
1539
1540
1541
1542

1543
1544
1545
1546
1547
1548
1549

1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550








+








  u8 colFlags;     /* Boolean properties.  See COLFLAG_ defines below */
};

/* Allowed values for Column.colFlags:
*/
#define COLFLAG_PRIMKEY  0x0001    /* Column is part of the primary key */
#define COLFLAG_HIDDEN   0x0002    /* A hidden column in a virtual table */
#define COLFLAG_NOAUTO   0x0004    /* Not useful in an automatic index */

/*
** A "Collating Sequence" is defined by an instance of the following
** structure. Conceptually, a collating sequence consists of a name and
** a comparison routine that defines the order of that sequence.
**
** If CollSeq.xCmp is NULL, it means that the
︙

Changes to src/where.c.

︙
574
575
576
577
578
579
580
581


582
583

584
585
586
587
588


589

590
591
592
593
594
595
596

574
575
576
577
578
579
580

581
582
583
584
585
586
587
588
589

590
591
592
593
594
595
596
597
598
599
600








-
+
+


+




-
+
+

+








** Return TRUE if the WHERE clause term pTerm is of a form where it
** could be used with an index to access pSrc, assuming an appropriate
** index existed.
*/
static int termCanDriveIndex(
  WhereTerm *pTerm,              /* WHERE clause term to check */
  struct SrcList_item *pSrc,     /* Table we are trying to access */
  Bitmask notReady               /* Tables in outer loops of the join */
  Bitmask notReady,              /* Tables in outer loops of the join */
  int excludeNoauto              /* Answer FALSE if pTerm is COLFLAG_NOAUTO */
){
  char aff;
  Column *pCol;
  if( pTerm->leftCursor!=pSrc->iCursor ) return 0;
  if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) return 0;
  if( (pTerm->prereqRight & notReady)!=0 ) return 0;
  if( pTerm->u.leftColumn<0 ) return 0;
  aff = pSrc->pTab->aCol[pTerm->u.leftColumn].affinity;
  pCol = &pSrc->pTab->aCol[pTerm->u.leftColumn];
  aff = pCol->affinity;
  if( !sqlite3IndexAffinityOk(pTerm->pExpr, aff) ) return 0;
  if( excludeNoauto && (pCol->colFlags & COLFLAG_NOAUTO)!=0 ) return 0;
  testcase( pTerm->pExpr->op==TK_IS );
  return 1;
}
#endif


#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
︙
651
652
653
654
655
656
657
658

659
660
661
662
663
664
665

655
656
657
658
659
660
661

662
663
664
665
666
667
668
669








-
+








    if( pLoop->prereq==0
     && (pTerm->wtFlags & TERM_VIRTUAL)==0
     && !ExprHasProperty(pExpr, EP_FromJoin)
     && sqlite3ExprIsTableConstant(pExpr, pSrc->iCursor) ){
      pPartial = sqlite3ExprAnd(pParse->db, pPartial,
                                sqlite3ExprDup(pParse->db, pExpr, 0));
    }
    if( termCanDriveIndex(pTerm, pSrc, notReady) ){
    if( termCanDriveIndex(pTerm, pSrc, notReady, 0) ){
      int iCol = pTerm->u.leftColumn;
      Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol);
      testcase( iCol==BMS );
      testcase( iCol==BMS-1 );
      if( !sentWarning ){
        sqlite3_log(SQLITE_WARNING_AUTOINDEX,
            "automatic index on %s(%s)", pTable->zName,
︙
704
705
706
707
708
709
710
711

712
713
714
715
716
717
718

708
709
710
711
712
713
714

715
716
717
718
719
720
721
722








-
+








  if( pIdx==0 ) goto end_auto_index_create;
  pLoop->u.btree.pIndex = pIdx;
  pIdx->zName = "auto-index";
  pIdx->pTable = pTable;
  n = 0;
  idxCols = 0;
  for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
    if( termCanDriveIndex(pTerm, pSrc, notReady) ){
    if( termCanDriveIndex(pTerm, pSrc, notReady, 0) ){
      int iCol = pTerm->u.leftColumn;
      Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol);
      testcase( iCol==BMS-1 );
      testcase( iCol==BMS );
      if( (idxCols & cMask)==0 ){
        Expr *pX = pTerm->pExpr;
        idxCols |= cMask;
︙
2623
2624
2625
2626
2627
2628
2629
2630

2631
2632
2633
2634
2635
2636
2637

2627
2628
2629
2630
2631
2632
2633

2634
2635
2636
2637
2638
2639
2640
2641








-
+








   && !pSrc->fg.isRecursive  /* Not a recursive common table expression. */
  ){
    /* Generate auto-index WhereLoops */
    WhereTerm *pTerm;
    WhereTerm *pWCEnd = pWC->a + pWC->nTerm;
    for(pTerm=pWC->a; rc==SQLITE_OK && pTerm<pWCEnd; pTerm++){
      if( pTerm->prereqRight & pNew->maskSelf ) continue;
      if( termCanDriveIndex(pTerm, pSrc, 0) ){
      if( termCanDriveIndex(pTerm, pSrc, 0, 1) ){
        pNew->u.btree.nEq = 1;
        pNew->nSkip = 0;
        pNew->u.btree.pIndex = 0;
        pNew->nLTerm = 1;
        pNew->aLTerm[0] = pTerm;
        /* TUNING: One-time cost for computing the automatic index is
        ** estimated to be X*N*log2(N) where N is the number of rows in
︙

This page was generated in about 0.07s by Fossil 2.24 [71919ad1b5] 2024-04-17 13:27:34