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
Side-by-Side 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

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 iCol;
  int ii;
  int ii, jj;

  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 ){
      for(jj=0; jj<pIndex->nColumn; jj++){
      if( pExpr->iColumn==iCol ) return 1;
        if( pExpr->iColumn==pIndex->aiColumn[jj] ) return 1;
      return 0;
      }
    }
  }
  return 0;
}

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