SQLite

Check-in [ade473b5ae]
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
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.642
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: b920bb70bb 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: ade473b5ae 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: e8b7ea8202 user: drh tags: nextgen-query-plan-exp)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
︙︙
4366
4367
4368
4369
4370
4371
4372
4373
4374
4375
4376
4377
4378
4379
4380
4381
4382

4383
4384

4385
4386
4387
4388
4389
4390
4391

*/
static int indexMightHelpWithOrderBy(
  WhereLoopBuilder *pBuilder,
  Index *pIndex,
  int iCursor
){
  ExprList *pOB;
  int iCol;
  int ii;

  if( pIndex->bUnordered ) return 0;
  if( (pOB = pBuilder->pWInfo->pOrderBy)==0 ) return 0;
  iCol = pIndex->aiColumn[0];
  for(ii=0; ii<pOB->nExpr; ii++){
    Expr *pExpr = sqlite3ExprSkipCollate(pOB->a[ii].pExpr);
    if( pExpr->op!=TK_COLUMN ) return 0;
    if( pExpr->iTable==iCursor ){

      if( pExpr->iColumn==iCol ) return 1;
      return 0;

    }
  }
  return 0;
}

/*
** Return a bitmask where 1s indicate that the corresponding column of








<
|



<




>
|
<
>








4366
4367
4368
4369
4370
4371
4372

4373
4374
4375
4376

4377
4378
4379
4380
4381
4382

4383
4384
4385
4386
4387
4388
4389
4390

*/
static int indexMightHelpWithOrderBy(
  WhereLoopBuilder *pBuilder,
  Index *pIndex,
  int iCursor
){
  ExprList *pOB;

  int ii, jj;

  if( pIndex->bUnordered ) return 0;
  if( (pOB = pBuilder->pWInfo->pOrderBy)==0 ) return 0;

  for(ii=0; ii<pOB->nExpr; ii++){
    Expr *pExpr = sqlite3ExprSkipCollate(pOB->a[ii].pExpr);
    if( pExpr->op!=TK_COLUMN ) return 0;
    if( pExpr->iTable==iCursor ){
      for(jj=0; jj<pIndex->nColumn; jj++){
        if( pExpr->iColumn==pIndex->aiColumn[jj] ) return 1;

      }
    }
  }
  return 0;
}

/*
** Return a bitmask where 1s indicate that the corresponding column of
︙︙