/ Check-in [294ba6f7]
Login

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

Overview
Comment:Scan an index instead of a table for "SELECT count(*) FROM <tbl>" queries. Because an index is usually smaller than a table on disk, this saves some IO. (CVS 6315)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:294ba6f743c9132dce0e73da480bd3c2071e7239
User & Date: danielk1977 2009-02-23 17:33:50
Context
2009-02-24
10:01
Optimize queries of the form "SELECT count(*) FROM <tbl>" by adding a sqlite3BtreeCount() interface to the btree layer. (CVS 6316) check-in: d4aa6593 user: danielk1977 tags: trunk
2009-02-23
17:33
Scan an index instead of a table for "SELECT count(*) FROM <tbl>" queries. Because an index is usually smaller than a table on disk, this saves some IO. (CVS 6315) check-in: 294ba6f7 user: danielk1977 tags: trunk
16:52
Add the reverse_unordered_selects pragma. (CVS 6314) check-in: bc078e00 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/delete.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
391
392
393
394
395
396
397

398
399
400
401
402
403
404
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** in order to generate code for DELETE FROM statements.
**
** $Id: delete.c,v 1.193 2009/02/20 10:58:42 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** Look up every table that is named in pSrc.  If any table is not found,
** add an error message to pParse->zErrMsg and return NULL.  If all tables
** are found, return a pointer to the last table.
................................................................................
  {
    int iRowid = ++pParse->nMem;    /* Used for storing rowid values. */
    int iRowSet = ++pParse->nMem;   /* Register for rowset of rows to delete */

    /* Collect rowids of every row to be deleted.
    */
    sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet);

    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0,
                               WHERE_FILL_ROWSET, iRowSet);
    if( pWInfo==0 ) goto delete_from_cleanup;
    if( db->flags & SQLITE_CountRows ){
      sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1);
    }
    sqlite3WhereEnd(pWInfo);







|







 







>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** in order to generate code for DELETE FROM statements.
**
** $Id: delete.c,v 1.194 2009/02/23 17:33:50 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** Look up every table that is named in pSrc.  If any table is not found,
** add an error message to pParse->zErrMsg and return NULL.  If all tables
** are found, return a pointer to the last table.
................................................................................
  {
    int iRowid = ++pParse->nMem;    /* Used for storing rowid values. */
    int iRowSet = ++pParse->nMem;   /* Register for rowset of rows to delete */

    /* Collect rowids of every row to be deleted.
    */
    sqlite3VdbeAddOp2(v, OP_Null, 0, iRowSet);
    pTabList->a[0].usesRowid = 1;
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0,
                               WHERE_FILL_ROWSET, iRowSet);
    if( pWInfo==0 ) goto delete_from_cleanup;
    if( db->flags & SQLITE_CountRows ){
      sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1);
    }
    sqlite3WhereEnd(pWInfo);

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
927
928
929
930
931
932
933

934
935
936
937
938
939
940
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.414 2009/02/20 10:58:42 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** Return the 'affinity' of the expression pExpr if any.
**
** If pExpr is a column, a reference to a column via an 'AS' alias,
................................................................................
    if( pTab ){
      pTab->nRef++;
    }
    pNewItem->pSelect = sqlite3SelectDup(db, pOldItem->pSelect, flags);
    pNewItem->pOn = sqlite3ExprDup(db, pOldItem->pOn, flags);
    pNewItem->pUsing = sqlite3IdListDup(db, pOldItem->pUsing);
    pNewItem->colUsed = pOldItem->colUsed;

  }
  return pNew;
}
IdList *sqlite3IdListDup(sqlite3 *db, IdList *p){
  IdList *pNew;
  int i;
  if( p==0 ) return 0;







|







 







>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.415 2009/02/23 17:33:50 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** Return the 'affinity' of the expression pExpr if any.
**
** If pExpr is a column, a reference to a column via an 'AS' alias,
................................................................................
    if( pTab ){
      pTab->nRef++;
    }
    pNewItem->pSelect = sqlite3SelectDup(db, pOldItem->pSelect, flags);
    pNewItem->pOn = sqlite3ExprDup(db, pOldItem->pOn, flags);
    pNewItem->pUsing = sqlite3IdListDup(db, pOldItem->pUsing);
    pNewItem->colUsed = pOldItem->colUsed;
    pNewItem->usesRowid = pOldItem->usesRowid;
  }
  return pNew;
}
IdList *sqlite3IdListDup(sqlite3 *db, IdList *p){
  IdList *pNew;
  int i;
  if( p==0 ) return 0;

Changes to src/resolve.c.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
343
344
345
346
347
348
349

350
351
352
353
354
355
356
357



358
359
360
361
362
363
364
**
*************************************************************************
**
** This file contains routines used for walking the parser tree and
** resolve all identifiers by associating them with a particular
** table and column.
**
** $Id: resolve.c,v 1.16 2009/02/19 14:39:25 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <stdlib.h>
#include <string.h>

/*
** Turn the pExpr expression into an alias for the iCol-th column of the
................................................................................

  /* If a column from a table in pSrcList is referenced, then record
  ** this fact in the pSrcList.a[].colUsed bitmask.  Column 0 causes
  ** bit 0 to be set.  Column 1 sets bit 1.  And so forth.  If the
  ** column number is greater than the number of bits in the bitmask
  ** then set the high-order bit of the bitmask.
  */

  if( pExpr->iColumn>=0 && pMatch!=0 ){
    int n = pExpr->iColumn;
    testcase( n==BMS-1 );
    if( n>=BMS ){
      n = BMS-1;
    }
    assert( pMatch->iCursor==pExpr->iTable );
    pMatch->colUsed |= ((Bitmask)1)<<n;



  }

lookupname_end:
  /* Clean up and return
  */
  sqlite3DbFree(db, zDb);
  sqlite3DbFree(db, zTab);







|







 







>
|
|
|
|
|
|
|
|
>
>
>







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
**
*************************************************************************
**
** This file contains routines used for walking the parser tree and
** resolve all identifiers by associating them with a particular
** table and column.
**
** $Id: resolve.c,v 1.17 2009/02/23 17:33:50 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <stdlib.h>
#include <string.h>

/*
** Turn the pExpr expression into an alias for the iCol-th column of the
................................................................................

  /* If a column from a table in pSrcList is referenced, then record
  ** this fact in the pSrcList.a[].colUsed bitmask.  Column 0 causes
  ** bit 0 to be set.  Column 1 sets bit 1.  And so forth.  If the
  ** column number is greater than the number of bits in the bitmask
  ** then set the high-order bit of the bitmask.
  */
  if( pMatch ){
    if( pExpr->iColumn>=0 ){
      int n = pExpr->iColumn;
      testcase( n==BMS-1 );
      if( n>=BMS ){
        n = BMS-1;
      }
      assert( pMatch->iCursor==pExpr->iTable );
      pMatch->colUsed |= ((Bitmask)1)<<n;
    }else{
      pMatch->usesRowid = 1;
    }
  }

lookupname_end:
  /* Clean up and return
  */
  sqlite3DbFree(db, zDb);
  sqlite3DbFree(db, zTab);

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1597
1598
1599
1600
1601
1602
1603

1604
1605
1606
1607
1608
1609
1610
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.835 2009/02/23 16:52:08 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Include the configuration header output by 'configure' if we're using the
** autoconf-based build
................................................................................
    char *zName;      /* Name of the table */
    char *zAlias;     /* The "B" part of a "A AS B" phrase.  zName is the "A" */
    Table *pTab;      /* An SQL table corresponding to zName */
    Select *pSelect;  /* A SELECT statement used in place of a table name */
    u8 isPopulated;   /* Temporary table associated with SELECT is populated */
    u8 jointype;      /* Type of join between this able and the previous */
    u8 notIndexed;    /* True if there is a NOT INDEXED clause */

    int iCursor;      /* The VDBE cursor number used to access this table */
    Expr *pOn;        /* The ON clause of a join */
    IdList *pUsing;   /* The USING clause of a join */
    Bitmask colUsed;  /* Bit N (1<<N) set if column N of pTab is used */
    char *zIndex;     /* Identifier from "INDEXED BY <zIndex>" clause */
    Index *pIndex;    /* Index structure corresponding to zIndex, if any */
  } a[1];             /* One entry for each identifier on the list */







|







 







>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.836 2009/02/23 17:33:50 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Include the configuration header output by 'configure' if we're using the
** autoconf-based build
................................................................................
    char *zName;      /* Name of the table */
    char *zAlias;     /* The "B" part of a "A AS B" phrase.  zName is the "A" */
    Table *pTab;      /* An SQL table corresponding to zName */
    Select *pSelect;  /* A SELECT statement used in place of a table name */
    u8 isPopulated;   /* Temporary table associated with SELECT is populated */
    u8 jointype;      /* Type of join between this able and the previous */
    u8 notIndexed;    /* True if there is a NOT INDEXED clause */
    u8 usesRowid;     /* True if the rowid field is read */
    int iCursor;      /* The VDBE cursor number used to access this table */
    Expr *pOn;        /* The ON clause of a join */
    IdList *pUsing;   /* The USING clause of a join */
    Bitmask colUsed;  /* Bit N (1<<N) set if column N of pTab is used */
    char *zIndex;     /* Identifier from "INDEXED BY <zIndex>" clause */
    Index *pIndex;    /* Index structure corresponding to zIndex, if any */
  } a[1];             /* One entry for each identifier on the list */

Changes to src/update.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
339
340
341
342
343
344
345

346
347
348
349
350
351
352
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle UPDATE statements.
**
** $Id: update.c,v 1.193 2009/02/20 10:58:42 danielk1977 Exp $
*/
#include "sqliteInt.h"

#ifndef SQLITE_OMIT_VIRTUALTABLE
/* Forward declaration */
static void updateVirtualTable(
  Parse *pParse,       /* The parsing context */
................................................................................
  if( sqlite3ResolveExprNames(&sNC, pWhere) ){
    goto update_cleanup;
  }

  /* Begin the database scan
  */
  sqlite3VdbeAddOp2(v, OP_Null, 0, regOldRowid);

  pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0,
                             WHERE_ONEPASS_DESIRED, 0);
  if( pWInfo==0 ) goto update_cleanup;
  okOnePass = pWInfo->okOnePass;

  /* Remember the rowid of every item to be updated.
  */







|







 







>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle UPDATE statements.
**
** $Id: update.c,v 1.194 2009/02/23 17:33:50 danielk1977 Exp $
*/
#include "sqliteInt.h"

#ifndef SQLITE_OMIT_VIRTUALTABLE
/* Forward declaration */
static void updateVirtualTable(
  Parse *pParse,       /* The parsing context */
................................................................................
  if( sqlite3ResolveExprNames(&sNC, pWhere) ){
    goto update_cleanup;
  }

  /* Begin the database scan
  */
  sqlite3VdbeAddOp2(v, OP_Null, 0, regOldRowid);
  pTabList->a[0].usesRowid = 1;
  pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0,
                             WHERE_ONEPASS_DESIRED, 0);
  if( pWInfo==0 ) goto update_cleanup;
  okOnePass = pWInfo->okOnePass;

  /* Remember the rowid of every item to be updated.
  */

Changes to src/where.c.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
....
2030
2031
2032
2033
2034
2035
2036















2037
2038
2039
2040
2041
2042
2043
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is responsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.371 2009/02/23 16:52:08 drh Exp $
*/
#include "sqliteInt.h"

/*
** Trace output macros
*/
#if defined(SQLITE_TEST) || defined(SQLITE_DEBUG)
................................................................................
**       fewer the better.)
**
**    *  Whether or not sorting must occur.
**
**    *  Whether or not there must be separate lookups in the
**       index and in the main table.
**
** If there was an INDEXED BY clause attached to the table in the SELECT
** statement, then this function only considers plans using the 
** named index. If one cannot be found, then the returned cost is
** SQLITE_BIG_DBL. If a plan can be found that uses the named index, 
** then the cost is calculated in the usual way.
**
** If a NOT INDEXED clause was attached to the table in the SELECT 
** statement, then no indexes are considered. However, the selected 
** plan may still take advantage of the tables built-in rowid
** index.
*/
static void bestIndex(
  Parse *pParse,              /* The parsing context */
  WhereClause *pWC,           /* The WHERE clause */
  struct SrcList_item *pSrc,  /* The FROM clause term to search */
  Bitmask notReady,           /* Mask of cursors that are not available */
................................................................................
      pCost->nRow = nRow;
      pCost->plan.wsFlags = wsFlags;
      pCost->plan.nEq = nEq;
      assert( pCost->plan.wsFlags & WHERE_INDEXED );
      pCost->plan.u.pIdx = pProbe;
    }
  }
















  /* Report the best result
  */
  pCost->plan.wsFlags |= eqTermMask;
  WHERETRACE(("best index is %s, cost=%.9g, nrow=%.9g, wsFlags=%x, nEq=%d\n",
        (pCost->plan.wsFlags & WHERE_INDEXED)!=0 ?
             pCost->plan.u.pIdx->zName : "(none)", pCost->nRow,







|







 







|
|




|
|
|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
....
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is responsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.372 2009/02/23 17:33:50 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** Trace output macros
*/
#if defined(SQLITE_TEST) || defined(SQLITE_DEBUG)
................................................................................
**       fewer the better.)
**
**    *  Whether or not sorting must occur.
**
**    *  Whether or not there must be separate lookups in the
**       index and in the main table.
**
** If there was an INDEXED BY clause (pSrc->pIndex) attached to the table in
** the SQL statement, then this function only considers plans using the 
** named index. If one cannot be found, then the returned cost is
** SQLITE_BIG_DBL. If a plan can be found that uses the named index, 
** then the cost is calculated in the usual way.
**
** If a NOT INDEXED clause (pSrc->notIndexed!=0) was attached to the table 
** in the SELECT statement, then no indexes are considered. However, the 
** selected plan may still take advantage of the tables built-in rowid
** index.
*/
static void bestIndex(
  Parse *pParse,              /* The parsing context */
  WhereClause *pWC,           /* The WHERE clause */
  struct SrcList_item *pSrc,  /* The FROM clause term to search */
  Bitmask notReady,           /* Mask of cursors that are not available */
................................................................................
      pCost->nRow = nRow;
      pCost->plan.wsFlags = wsFlags;
      pCost->plan.nEq = nEq;
      assert( pCost->plan.wsFlags & WHERE_INDEXED );
      pCost->plan.u.pIdx = pProbe;
    }
  }

  if( pCost->plan.wsFlags==0 && pSrc->colUsed==0 && pSrc->usesRowid==0 ){
    Index *pSmallest = 0;
    assert( pSrc->pIndex==0 );
    for(pProbe=pSrc->pTab->pIndex; pProbe; pProbe=pProbe->pNext){
      if( !pSmallest || pProbe->nColumn<pSmallest->nColumn ){
        pSmallest = pProbe;
      }
    }
    if( pSmallest && pSmallest->nColumn<pSrc->pTab->nCol ){
      assert( pCost->plan.nEq==0 );
      pCost->plan.u.pIdx = pSmallest;
      pCost->plan.wsFlags = WHERE_COLUMN_RANGE|WHERE_IDX_ONLY;
    }
  }

  /* Report the best result
  */
  pCost->plan.wsFlags |= eqTermMask;
  WHERETRACE(("best index is %s, cost=%.9g, nrow=%.9g, wsFlags=%x, nEq=%d\n",
        (pCost->plan.wsFlags & WHERE_INDEXED)!=0 ?
             pCost->plan.u.pIdx->zName : "(none)", pCost->nRow,

Changes to test/where9.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
797
798
799
800
801
802
803
804


























805
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the multi-index OR clause optimizer.
#
# $Id: where9.test,v 1.5 2009/01/08 21:00:03 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !or_opt {
  finish_test
  return
................................................................................
  catchsql {
    UPDATE t1 INDEXED BY t1b SET a=a+100
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {1 {cannot use index: t1b}}



























finish_test







|







 








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the multi-index OR clause optimizer.
#
# $Id: where9.test,v 1.6 2009/02/23 17:33:50 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !or_opt {
  finish_test
  return
................................................................................
  catchsql {
    UPDATE t1 INDEXED BY t1b SET a=a+100
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {1 {cannot use index: t1b}}

do_test where9-7.1 {
  execsql {
    CREATE TABLE t5(a, b, c);
    EXPLAIN QUERY PLAN SELECT count(*) FROM t5;
  }
} {0 0 {TABLE t5}}
do_test where9-7.2 {
  execsql {
    CREATE INDEX t5i1 ON t5(a, b);
    EXPLAIN QUERY PLAN SELECT count(*) FROM t5;
  }
} {0 0 {TABLE t5 WITH INDEX t5i1}}
do_test where9-7.3 {
  execsql {
    CREATE INDEX t5i2 ON t5(b);
    EXPLAIN QUERY PLAN SELECT count(*) FROM t5;
  }
} {0 0 {TABLE t5 WITH INDEX t5i2}}
do_test where9-7.4 {
  execsql {
    CREATE TABLE t6(a, b, c);
    CREATE INDEX t6i1 ON t6(a, b, c);
    EXPLAIN QUERY PLAN SELECT count(*) FROM t6;
  }
} {0 0 {TABLE t6}}

finish_test