/ Check-in [ac6d0fba]
Login

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

Overview
Comment:Progress toward getting automatic indices to work. Still failing in corner cases.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | experimental
Files: files | file ages | folders
SHA1: ac6d0fba78eb9dcd69372e128d4a039aaff4b417
User & Date: drh 2010-04-06 18:28:21
Context
2010-04-06
18:51
Runs quicktest without hitting an assert now. Some tests get unexpected results still and there is a memory leak. check-in: a8224448 user: drh tags: experimental
18:28
Progress toward getting automatic indices to work. Still failing in corner cases. check-in: ac6d0fba user: drh tags: experimental
15:57
Automatically generate transient indices for tables in joins that would otherwise have to use a full table scan. check-in: 1b2a0412 user: drh tags: experimental
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  1653   1653     WhereCost *pCost            /* Lowest cost query plan */
  1654   1654   ){
  1655   1655     double nTableRow;           /* Rows in the input table */
  1656   1656     double logN;                /* log(nTableRow) */
  1657   1657     double costTempIdx;         /* per-query cost of the transient index */
  1658   1658     WhereTerm *pTerm;           /* A single term of the WHERE clause */
  1659   1659     WhereTerm *pWCEnd;          /* End of pWC->a[] */
         1660  +  Table *pTable;              /* Table tht might be indexed */
  1660   1661   
  1661   1662     if( (pCost->plan.wsFlags & WHERE_NOT_FULLSCAN)!=0 ){
  1662   1663       /* We already have some kind of index in use for this query. */
  1663   1664       return;
  1664   1665     }
  1665   1666     if( pSrc->notIndexed ){
  1666   1667       /* The NOT INDEXED clause appears in the SQL. */
................................................................................
  1674   1675     if( costTempIdx>=pCost->rCost ){
  1675   1676       /* The cost of creating the transient table would be greater than
  1676   1677       ** doing the full table scan */
  1677   1678       return;
  1678   1679     }
  1679   1680   
  1680   1681     /* Search for any equality comparison term */
         1682  +  pTable = pSrc->pTab;
  1681   1683     pWCEnd = &pWC->a[pWC->nTerm];
  1682   1684     for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
  1683   1685       if( pTerm->leftCursor==pSrc->iCursor
  1684   1686          && (pTerm->prereqRight & notReady)==0
  1685   1687          && (pTerm->eOperator & WO_EQ)!=0
         1688  +       && sqlite3IndexAffinityOk(pTerm->pExpr,
         1689  +                                 pTable->aCol[pTerm->u.leftColumn].affinity)
  1686   1690       ){
  1687   1691         WHERETRACE(("auto-index reduces cost from %.2f to %.2f\n",
  1688   1692                       pCost->rCost, costTempIdx));
  1689   1693         pCost->rCost = costTempIdx;
  1690   1694         pCost->nRow = logN + 1;
  1691   1695         pCost->plan.wsFlags = WHERE_TEMP_INDEX;
  1692   1696         pCost->used = pTerm->prereqRight;
................................................................................
  1715   1719     int regIsInit;              /* Register set by initialization */
  1716   1720     int addrInit;               /* Address of the initialization bypass jump */
  1717   1721     Table *pTable;              /* The table being indexed */
  1718   1722     KeyInfo *pKeyinfo;          /* Key information for the index */   
  1719   1723     int addrTop;                /* Top of the index fill loop */
  1720   1724     int regRecord;              /* Register holding an index record */
  1721   1725     int n;                      /* Column counter */
         1726  +  CollSeq *pColl;             /* Collating sequence to on a column */
  1722   1727   
  1723   1728     /* Generate code to skip over the creation and initialization of the
  1724   1729     ** transient index on 2nd and subsequent iterations of the loop. */
  1725   1730     v = pParse->pVdbe;
  1726   1731     assert( v!=0 );
  1727   1732     regIsInit = ++pParse->nMem;
  1728   1733     addrInit = sqlite3VdbeAddOp1(v, OP_If, regIsInit);
  1729   1734     sqlite3VdbeAddOp2(v, OP_Integer, 1, regIsInit);
  1730   1735   
  1731   1736     /* Count the number of columns that will be added to the index */
  1732   1737     nColumn = 0;
         1738  +  pTable = pSrc->pTab;
  1733   1739     pWCEnd = &pWC->a[pWC->nTerm];
  1734   1740     for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
  1735   1741       if( pTerm->leftCursor==pSrc->iCursor
  1736   1742          && (pTerm->prereqRight & notReady)==0
  1737   1743          && (pTerm->eOperator & WO_EQ)!=0
         1744  +       && sqlite3IndexAffinityOk(pTerm->pExpr,
         1745  +                                 pTable->aCol[pTerm->u.leftColumn].affinity)
  1738   1746       ){
  1739   1747         nColumn++;
  1740   1748       }
  1741   1749     }
  1742   1750     assert( nColumn>0 );
         1751  +  pLevel->plan.nEq = nColumn;
         1752  +  pLevel->plan.wsFlags = WHERE_COLUMN_EQ | WO_EQ;
  1743   1753   
  1744   1754     /* Construct the Index object to describe this index */
  1745   1755     nByte = sizeof(Index);
  1746   1756     nByte += nColumn*sizeof(int);     /* Index.aiColumn */
  1747   1757     nByte += nColumn*sizeof(char*);   /* Index.azColl */
  1748   1758     nByte += nColumn;                 /* Index.aSortOrder */
  1749   1759     pIdx = sqlite3DbMallocZero(pParse->db, nByte);
................................................................................
  1750   1760     if( pIdx==0 ) return;
  1751   1761     pLevel->plan.u.pIdx = pIdx;
  1752   1762     pIdx->azColl = (char**)&pIdx[1];
  1753   1763     pIdx->aiColumn = (int*)&pIdx->azColl[nColumn];
  1754   1764     pIdx->aSortOrder = (u8*)&pIdx->aiColumn[nColumn];
  1755   1765     pIdx->zName = "auto-index";
  1756   1766     pIdx->nColumn = nColumn;
  1757         -  pIdx->pTable = pTable = pSrc->pTab;
         1767  +  pIdx->pTable = pTable;
  1758   1768     n = 0;
  1759   1769     for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
  1760   1770       if( pTerm->leftCursor==pSrc->iCursor
  1761   1771          && (pTerm->prereqRight & notReady)==0
  1762   1772          && (pTerm->eOperator & WO_EQ)!=0
         1773  +       && sqlite3IndexAffinityOk(pTerm->pExpr,
         1774  +                                 pTable->aCol[pTerm->u.leftColumn].affinity)
  1763   1775       ){
  1764   1776         int iCol = pTerm->u.leftColumn;
         1777  +      Expr *pX;
  1765   1778         pIdx->aiColumn[n] = iCol;
  1766         -      pIdx->azColl[n] = pTable->aCol[iCol].zColl;
  1767         -      if( pIdx->azColl[n]==0 ) pIdx->azColl[n] = "BINARY";
         1779  +      pX = pTerm->pExpr;
         1780  +      pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
         1781  +      pIdx->azColl[n] = pColl->zName;
  1768   1782         n++;
  1769   1783       }
  1770   1784     }
  1771   1785     assert( n==pIdx->nColumn );
  1772   1786   
  1773   1787     /* Create the transient index */
  1774   1788     pKeyinfo = sqlite3IndexKeyinfo(pParse, pIdx);
  1775   1789     assert( pLevel->iIdxCur>=0 );
  1776   1790     sqlite3VdbeAddOp4(v, OP_OpenEphemeral, pLevel->iIdxCur, nColumn+1, 0,
  1777   1791                       (char*)pKeyinfo, P4_KEYINFO_HANDOFF);
         1792  +  VdbeComment((v, "auto-idx for %s", pTable->zName));
  1778   1793   
  1779   1794     /* Fill the transient index with content */
  1780   1795     addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, pLevel->iTabCur);
  1781   1796     regRecord = sqlite3GetTempReg(pParse);
  1782   1797     sqlite3GenerateIndexKey(pParse, pIdx, pLevel->iTabCur, regRecord, 1);
  1783   1798     sqlite3VdbeAddOp2(v, OP_IdxInsert, pLevel->iIdxCur, regRecord);
  1784   1799     sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
................................................................................
  4056   4071           zMsg = sqlite3MAppendf(db, zMsg, "%s ORDER BY", zMsg);
  4057   4072         }
  4058   4073         sqlite3VdbeAddOp4(v, OP_Explain, i, pLevel->iFrom, 0, zMsg, P4_DYNAMIC);
  4059   4074       }
  4060   4075   #endif /* SQLITE_OMIT_EXPLAIN */
  4061   4076       pTabItem = &pTabList->a[pLevel->iFrom];
  4062   4077       pTab = pTabItem->pTab;
         4078  +    pLevel->iTabCur = pTabItem->iCursor;
  4063   4079       iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
  4064         -    if( (pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect ) continue;
         4080  +    if( (pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect ){
         4081  +      if( pLevel->plan.wsFlags & WHERE_TEMP_INDEX ){
         4082  +        constructTransientIndex(pParse, pWC, pTabItem, notReady, pLevel);
         4083  +      }
         4084  +      continue;
         4085  +    }
  4065   4086   #ifndef SQLITE_OMIT_VIRTUALTABLE
  4066   4087       if( (pLevel->plan.wsFlags & WHERE_VIRTUALTABLE)!=0 ){
  4067   4088         const char *pVTab = (const char *)sqlite3GetVTable(db, pTab);
  4068   4089         int iCur = pTabItem->iCursor;
  4069   4090         sqlite3VdbeAddOp4(v, OP_VOpen, iCur, 0, 0, pVTab, P4_VTAB);
  4070   4091       }else
  4071   4092   #endif
................................................................................
  4080   4101           sqlite3VdbeChangeP4(v, sqlite3VdbeCurrentAddr(v)-1, 
  4081   4102                               SQLITE_INT_TO_PTR(n), P4_INT32);
  4082   4103           assert( n<=pTab->nCol );
  4083   4104         }
  4084   4105       }else{
  4085   4106         sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
  4086   4107       }
  4087         -    pLevel->iTabCur = pTabItem->iCursor;
  4088   4108       if( (pLevel->plan.wsFlags & WHERE_TEMP_INDEX)!=0 ){
  4089   4109         constructTransientIndex(pParse, pWC, pTabItem, notReady, pLevel);
  4090   4110       }else if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 ){
  4091   4111         Index *pIx = pLevel->plan.u.pIdx;
  4092   4112         KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIx);
  4093   4113         int iIdxCur = pLevel->iIdxCur;
  4094   4114         assert( pIx->pSchema==pTab->pSchema );