/ Check-in [ade473b5]
Login

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

Overview
Comment:An index might be useful for ORDER BY if any indexed column is in the ORDER BY clause, not just the first indexed column.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1: ade473b5ae3fe2162b0ec29731d8e864a9301e07
User & Date: drh 2013-06-13 17:58:08
Context
2013-06-14
02:51
Add a new ORDER BY optimization that bypasses ORDER BY terms that are constrained by == and IS NULL terms of the WHERE clause. check-in: b920bb70 user: drh tags: nextgen-query-plan-exp
2013-06-13
17:58
An index might be useful for ORDER BY if any indexed column is in the ORDER BY clause, not just the first indexed column. check-in: ade473b5 user: drh tags: nextgen-query-plan-exp
17:28
Make sure that disabling the covering index scan optimization does not prevent a covering index from being used to satisfy an ORDER BY clause. check-in: e8b7ea82 user: drh tags: nextgen-query-plan-exp
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  4366   4366   */
  4367   4367   static int indexMightHelpWithOrderBy(
  4368   4368     WhereLoopBuilder *pBuilder,
  4369   4369     Index *pIndex,
  4370   4370     int iCursor
  4371   4371   ){
  4372   4372     ExprList *pOB;
  4373         -  int iCol;
  4374         -  int ii;
         4373  +  int ii, jj;
  4375   4374   
  4376   4375     if( pIndex->bUnordered ) return 0;
  4377   4376     if( (pOB = pBuilder->pWInfo->pOrderBy)==0 ) return 0;
  4378         -  iCol = pIndex->aiColumn[0];
  4379   4377     for(ii=0; ii<pOB->nExpr; ii++){
  4380   4378       Expr *pExpr = sqlite3ExprSkipCollate(pOB->a[ii].pExpr);
  4381   4379       if( pExpr->op!=TK_COLUMN ) return 0;
  4382   4380       if( pExpr->iTable==iCursor ){
  4383         -      if( pExpr->iColumn==iCol ) return 1;
  4384         -      return 0;
         4381  +      for(jj=0; jj<pIndex->nColumn; jj++){
         4382  +        if( pExpr->iColumn==pIndex->aiColumn[jj] ) return 1;
         4383  +      }
  4385   4384       }
  4386   4385     }
  4387   4386     return 0;
  4388   4387   }
  4389   4388   
  4390   4389   /*
  4391   4390   ** Return a bitmask where 1s indicate that the corresponding column of