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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
294ba6f743c9132dce0e73da480bd3c2 |
User & Date: | danielk1977 2009-02-23 17:33:50.000 |
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: d4aa659318 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: 294ba6f743 user: danielk1977 tags: trunk) | |
16:52 | Add the reverse_unordered_selects pragma. (CVS 6314) (check-in: bc078e0007 user: drh tags: trunk) | |
Changes
Changes to src/delete.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** 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. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** 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. |
︙ | ︙ | |||
391 392 393 394 395 396 397 398 399 400 401 402 403 404 | { 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); | > | 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 | { 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 | ** 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. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** 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, |
︙ | ︙ | |||
927 928 929 930 931 932 933 934 935 936 937 938 939 940 | 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; | > | 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 | 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 | ** ************************************************************************* ** ** This file contains routines used for walking the parser tree and ** resolve all identifiers by associating them with a particular ** table and column. ** | | | 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | ** ************************************************************************* ** ** 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 |
︙ | ︙ | |||
343 344 345 346 347 348 349 | /* 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. */ | > | | | | | | | | > > > | 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 | /* 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /* ** 2001 September 15 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** 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. ** | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | /* ** 2001 September 15 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** 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 |
︙ | ︙ | |||
1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 | 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 */ | > | 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 | 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 | ** 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. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** 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 */ |
︙ | ︙ | |||
339 340 341 342 343 344 345 346 347 348 349 350 351 352 | 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. */ | > | 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 | 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 | ** 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". ** | | | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | ** 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) |
︙ | ︙ | |||
1711 1712 1713 1714 1715 1716 1717 | ** 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. ** | | | | | | | 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 | ** 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 */ |
︙ | ︙ | |||
2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 | 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, | > > > > > > > > > > > > > > > | 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 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2008 December 30 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # 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. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the multi-index OR clause optimizer. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2008 December 30 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # 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. # #*********************************************************************** # 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 |
︙ | ︙ | |||
797 798 799 800 801 802 803 804 805 | 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 | > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | 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 |