/ Check-in [ade473b5]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 Unified Diffs 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