Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Minor fixes and documentation improvements for sqlite3_stmt_scanstatus(). |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | scanstatus |
Files: | files | file ages | folders |
SHA1: |
8d8cc9608d30bb65fffcfe488e904411 |
User & Date: | dan 2014-11-01 18:08:04.130 |
Context
2014-11-01
| ||
20:38 | If SQLITE_ENABLE_STMT_SCANSTATUS is defined, record the number of times each VDBE opcode is executed. Derive the values returned by sqlite3_stmt_scanstatus() from these records on demand. (check-in: 9ea37422a8 user: dan tags: scanstatus) | |
18:08 | Minor fixes and documentation improvements for sqlite3_stmt_scanstatus(). (check-in: 8d8cc9608d user: dan tags: scanstatus) | |
2014-10-31
| ||
20:11 | Add the experimental sqlite3_stmt_scanstatus() API. (check-in: 6a9bab34ae user: dan tags: scanstatus) | |
Changes
Changes to src/sqlite.h.in.
︙ | ︙ | |||
7404 7405 7406 7407 7408 7409 7410 7411 7412 7413 7414 7415 7416 7417 | /* #define SQLITE_IGNORE 2 // Also used by sqlite3_authorizer() callback */ #define SQLITE_FAIL 3 /* #define SQLITE_ABORT 4 // Also an error code */ #define SQLITE_REPLACE 5 /* ** Return status data for a single loop within query pStmt. ** ** Parameter "idx" identifies the specific loop to retrieve statistics for. ** Loops are numbered starting from zero. If idx is out of range - less than ** zero or greater than or equal to the total number of loops used to implement ** the statement - a non-zero value is returned. In this case the final value ** of all five output parameters is undefined. Otherwise, if idx is in range, | > > | > > > > > > > > > > > > > > > > > > > > | 7404 7405 7406 7407 7408 7409 7410 7411 7412 7413 7414 7415 7416 7417 7418 7419 7420 7421 7422 7423 7424 7425 7426 7427 7428 7429 7430 7431 7432 7433 7434 7435 7436 7437 7438 7439 7440 7441 7442 7443 7444 7445 7446 7447 7448 7449 7450 7451 7452 7453 7454 7455 7456 7457 7458 7459 7460 7461 7462 7463 7464 7465 7466 7467 | /* #define SQLITE_IGNORE 2 // Also used by sqlite3_authorizer() callback */ #define SQLITE_FAIL 3 /* #define SQLITE_ABORT 4 // Also an error code */ #define SQLITE_REPLACE 5 /* ** CAPI3REF: Prepared Statement Scan Statuses ** ** Return status data for a single loop within query pStmt. ** ** Parameter "idx" identifies the specific loop to retrieve statistics for. ** Loops are numbered starting from zero. If idx is out of range - less than ** zero or greater than or equal to the total number of loops used to implement ** the statement - a non-zero value is returned. In this case the final value ** of all five output parameters is undefined. Otherwise, if idx is in range, ** zero is returned and the output parameters set as follows: ** ** <ul> ** <li> (*pnLoop) is set to the total number of times the loop has been run. ** <li> (*pnVisit) is set to the total number of rows visited by the loop. ** <li> (*pnEst) is set to the estimate of the number of rows visited ** by each run of the loop used by the SQL optimizer. Ideally, this ** value should be close to (*pnVisit)/(*pnLoop). ** <li> (*pzName) is set to point to a nul-terminated string containing the ** name of the index of table used by this loop. ** <li> (*pzExplain) is set to point to a nul-terminated string containing ** same text that would be returned for this loop by an EXPLAIN ** QUERY PLAN command. ** </ul> ** ** Output parameters *pzName and *pzExplain are set to point to buffers ** managed by the statement object. Both of these pointers may be invalidated ** by any API call on the same statement object, including an sqlite3_step() ** sqlite3_bind_*() call. ** ** Statistics may not be available for all loops in all statements. In cases ** where there exist loops with no available statistics, this function ignores ** them completely. ** ** This API is only available if the library is built with pre-processor ** symbol SQLITE_ENABLE_STMT_SCANSTATUS defined. */ SQLITE_EXPERIMENTAL int sqlite3_stmt_scanstatus( sqlite3_stmt *pStmt, int idx, /* Index of loop to report on */ sqlite3_int64 *pnLoop, /* OUT: Number of times loop was run */ sqlite3_int64 *pnVisit, /* OUT: Number of rows visited (all loops) */ sqlite3_int64 *pnEst, /* OUT: Number of rows estimated (per loop) */ const char **pzName, /* OUT: Object name (table or index) */ const char **pzExplain /* OUT: EQP string */ ); /* ** CAPI3REF: Zero Scan-Status Counters ** ** Zero all sqlite3_stmt_scanstatus() related event counters. ** ** This API is only available if the library is built with pre-processor ** symbol SQLITE_ENABLE_STMT_SCANSTATUS defined. */ SQLITE_EXPERIMENTAL void sqlite3_stmt_scanstatus_reset(sqlite3_stmt*); |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
3885 3886 3887 3888 3889 3890 3891 3892 3893 3894 3895 3896 3897 3898 3899 3900 3901 3902 3903 3904 3905 | assert( pC->isTable ); assert( pC->pseudoTableReg==0 ); pCrsr = pC->pCursor; assert( pCrsr!=0 ); res = 0; iKey = pIn3->u.i; rc = sqlite3BtreeMovetoUnpacked(pCrsr, 0, iKey, 0, &res); pC->movetoTarget = iKey; /* Used by OP_Delete */ pC->nullRow = 0; pC->cacheStatus = CACHE_STALE; pC->deferredMoveto = 0; VdbeBranchTaken(res!=0,2); if( res!=0 ){ pc = pOp->p2 - 1; } pC->seekResult = res; break; } /* Opcode: Sequence P1 P2 * * * ** Synopsis: r[P2]=cursor[P1].ctr++ | > > > | 3885 3886 3887 3888 3889 3890 3891 3892 3893 3894 3895 3896 3897 3898 3899 3900 3901 3902 3903 3904 3905 3906 3907 3908 | assert( pC->isTable ); assert( pC->pseudoTableReg==0 ); pCrsr = pC->pCursor; assert( pCrsr!=0 ); res = 0; iKey = pIn3->u.i; rc = sqlite3BtreeMovetoUnpacked(pCrsr, 0, iKey, 0, &res); IncrementExplainCounter(pC, nLoop); pC->movetoTarget = iKey; /* Used by OP_Delete */ pC->nullRow = 0; pC->cacheStatus = CACHE_STALE; pC->deferredMoveto = 0; VdbeBranchTaken(res!=0,2); if( res!=0 ){ pc = pOp->p2 - 1; }else{ IncrementExplainCounter(pC, nVisit); } pC->seekResult = res; break; } /* Opcode: Sequence P1 P2 * * * ** Synopsis: r[P2]=cursor[P1].ctr++ |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
2735 2736 2737 2738 2739 2740 2741 | } } } *pzAff = zAff; return regBase; } | | | 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 2745 2746 2747 2748 2749 | } } } *pzAff = zAff; return regBase; } #if !defined(SQLITE_OMIT_EXPLAIN) || defined(SQLITE_ENABLE_STMT_SCANSTATUS) /* ** This routine is a helper for explainIndexRange() below ** ** pStr holds the text of an expression that we are building up one term ** at a time. This routine adds a new term to the end of the expression. ** Terms are separated by AND so add the "AND" text for second and subsequent ** terms only. |
︙ | ︙ | |||
2811 2812 2813 2814 2815 2816 2817 | ** command. If the query being compiled is an EXPLAIN QUERY PLAN, a single ** record is added to the output to describe the table scan strategy in ** pLevel. */ static void explainOneScan( Parse *pParse, /* Parse context */ SrcList *pTabList, /* Table list this loop refers to */ | | < > > | > > > | | 2811 2812 2813 2814 2815 2816 2817 2818 2819 2820 2821 2822 2823 2824 2825 2826 2827 2828 2829 2830 2831 2832 2833 2834 2835 2836 2837 2838 2839 2840 2841 2842 2843 2844 2845 2846 2847 2848 2849 2850 2851 2852 2853 | ** command. If the query being compiled is an EXPLAIN QUERY PLAN, a single ** record is added to the output to describe the table scan strategy in ** pLevel. */ static void explainOneScan( Parse *pParse, /* Parse context */ SrcList *pTabList, /* Table list this loop refers to */ WhereInfo *pWInfo, /* WHERE clause this loop belongs to */ int iLevel, /* Value for "level" column of output */ u16 wctrlFlags /* Flags passed to sqlite3WhereBegin() */ ){ #if !defined(SQLITE_DEBUG) && !defined(SQLITE_ENABLE_STMT_SCANSTATUS) if( pParse->explain==2 ) #endif { WhereLevel *pLevel = &pWInfo->a[iLevel]; struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom]; Vdbe *v = pParse->pVdbe; /* VM being constructed */ sqlite3 *db = pParse->db; /* Database handle */ #ifdef SQLITE_OMIT_EXPLAIN int iId = 0; /* Select id (left-most output column) */ #else int iId = pParse->iSelectId; /* Select id (left-most output column) */ #endif int isSearch; /* True for a SEARCH. False for SCAN. */ WhereLoop *pLoop; /* The controlling WhereLoop object */ u32 flags; /* Flags that describe this loop */ StrAccum str; /* EQP output string */ char zBuf[100]; /* Initial space for EQP output string */ const char *zObj; ExplainArg *pExplain; i64 nEstRow; /* Estimated rows per scan of pLevel */ pLoop = pLevel->pWLoop; flags = pLoop->wsFlags; if( (flags&WHERE_MULTI_OR) ) return; sqlite3StrAccumInit(&str, zBuf, sizeof(zBuf), SQLITE_MAX_LENGTH); str.db = db; /* Reserve space at the start of the buffer managed by the StrAccum ** object for *pExplain. */ assert( sizeof(*pExplain)<=sizeof(zBuf) ); |
︙ | ︙ | |||
2861 2862 2863 2864 2865 2866 2867 2868 2869 2870 2871 2872 2873 2874 2875 | /* Append the EQP text to the buffer */ isSearch = (flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))!=0 || ((flags&WHERE_VIRTUALTABLE)==0 && (pLoop->u.btree.nEq>0)) || (wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX)); sqlite3StrAccumAppendAll(&str, isSearch ? "SEARCH" : "SCAN"); if( pItem->pSelect ){ sqlite3XPrintf(&str, 0, " SUBQUERY %d", pItem->iSelectId); }else{ sqlite3XPrintf(&str, 0, " TABLE %s", pItem->zName); } if( pItem->zAlias ){ sqlite3XPrintf(&str, 0, " AS %s", pItem->zAlias); } | > > > > | 2865 2866 2867 2868 2869 2870 2871 2872 2873 2874 2875 2876 2877 2878 2879 2880 2881 2882 2883 | /* Append the EQP text to the buffer */ isSearch = (flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))!=0 || ((flags&WHERE_VIRTUALTABLE)==0 && (pLoop->u.btree.nEq>0)) || (wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX)); sqlite3StrAccumAppendAll(&str, isSearch ? "SEARCH" : "SCAN"); if( pItem->pSelect ){ #ifdef SQLITE_OMIT_EXPLAIN sqlite3XPrintf(&str, 0, " SUBQUERY 0"); #else sqlite3XPrintf(&str, 0, " SUBQUERY %d", pItem->iSelectId); #endif }else{ sqlite3XPrintf(&str, 0, " TABLE %s", pItem->zName); } if( pItem->zAlias ){ sqlite3XPrintf(&str, 0, " AS %s", pItem->zAlias); } |
︙ | ︙ | |||
2933 2934 2935 2936 2937 2938 2939 | pExplain->iCsr = pLevel->iTabCur; }else{ pExplain->iCsr = -1; } pExplain->nEst = nEstRow; pExplain->zName = (const char*)&pExplain[1]; pExplain->zExplain = &pExplain->zName[sqlite3Strlen30(pExplain->zName)+1]; | | | | | | 2941 2942 2943 2944 2945 2946 2947 2948 2949 2950 2951 2952 2953 2954 2955 2956 2957 2958 2959 2960 2961 2962 2963 | pExplain->iCsr = pLevel->iTabCur; }else{ pExplain->iCsr = -1; } pExplain->nEst = nEstRow; pExplain->zName = (const char*)&pExplain[1]; pExplain->zExplain = &pExplain->zName[sqlite3Strlen30(pExplain->zName)+1]; pWInfo->iExplain = sqlite3VdbeAddOp4(v, OP_Explain, iId, iLevel, pLevel->iFrom, (char*)pExplain,P4_EXPLAIN ); } } } #else # define explainOneScan(v,w,x,y,z) #endif /* !SQLITE_OMIT_EXPLAIN || SQLITE_ENABLE_STMT_SCANSTATUS */ /* ** Generate code for the start of the iLevel-th loop in the WHERE clause ** implementation described by pWInfo. */ static Bitmask codeOneLoopStart( |
︙ | ︙ | |||
3609 3610 3611 3612 3613 3614 3615 | /* Loop through table entries that match term pOrTerm. */ WHERETRACE(0xffff, ("Subplan for OR-clause:\n")); pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrExpr, 0, 0, wctrlFlags, iCovCur); assert( pSubWInfo || pParse->nErr || db->mallocFailed ); if( pSubWInfo ){ WhereLoop *pSubLoop; | | > > > | > > > | | 3617 3618 3619 3620 3621 3622 3623 3624 3625 3626 3627 3628 3629 3630 3631 3632 3633 3634 3635 3636 3637 3638 3639 | /* Loop through table entries that match term pOrTerm. */ WHERETRACE(0xffff, ("Subplan for OR-clause:\n")); pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrExpr, 0, 0, wctrlFlags, iCovCur); assert( pSubWInfo || pParse->nErr || db->mallocFailed ); if( pSubWInfo ){ WhereLoop *pSubLoop; /* If an OP_Explain was added for this sub-loop, fix the P2 and ** P3 parameters to it so that they are relative to the current ** context. */ if( pSubWInfo->iExplain!=0 ){ sqlite3VdbeChangeP2(v, pSubWInfo->iExplain, iLevel); sqlite3VdbeChangeP3(v, pSubWInfo->iExplain, pLevel->iFrom); } /* This is the sub-WHERE clause body. First skip over ** duplicate rows from prior sub-WHERE clauses, and record the ** rowid (or PRIMARY KEY) for the current row so that the same ** row will be skipped in subsequent sub-WHERE clauses. */ if( (pWInfo->wctrlFlags & WHERE_DUPLICATES_OK)==0 ){ int r; |
︙ | ︙ | |||
6451 6452 6453 6454 6455 6456 6457 | #ifndef SQLITE_OMIT_AUTOMATIC_INDEX if( (pLevel->pWLoop->wsFlags & WHERE_AUTO_INDEX)!=0 ){ constructAutomaticIndex(pParse, &pWInfo->sWC, &pTabList->a[pLevel->iFrom], notReady, pLevel); if( db->mallocFailed ) goto whereBeginError; } #endif | | | 6465 6466 6467 6468 6469 6470 6471 6472 6473 6474 6475 6476 6477 6478 6479 | #ifndef SQLITE_OMIT_AUTOMATIC_INDEX if( (pLevel->pWLoop->wsFlags & WHERE_AUTO_INDEX)!=0 ){ constructAutomaticIndex(pParse, &pWInfo->sWC, &pTabList->a[pLevel->iFrom], notReady, pLevel); if( db->mallocFailed ) goto whereBeginError; } #endif explainOneScan(pParse, pTabList, pWInfo, ii, wctrlFlags); pLevel->addrBody = sqlite3VdbeCurrentAddr(v); notReady = codeOneLoopStart(pWInfo, ii, notReady); pWInfo->iContinue = pLevel->addrCont; } /* Done. */ VdbeModuleComment((v, "Begin WHERE-core")); |
︙ | ︙ |
Changes to src/whereInt.h.
︙ | ︙ | |||
403 404 405 406 407 408 409 410 411 412 413 414 415 416 | u8 okOnePass; /* Ok to use one-pass algorithm for UPDATE/DELETE */ u8 untestedTerms; /* Not all WHERE terms resolved by outer loop */ u8 eDistinct; /* One of the WHERE_DISTINCT_* values below */ u8 nLevel; /* Number of nested loop */ int iTop; /* The very beginning of the WHERE loop */ int iContinue; /* Jump here to continue with next record */ int iBreak; /* Jump here to break out of the loop */ int savedNQueryLoop; /* pParse->nQueryLoop outside the WHERE loop */ int aiCurOnePass[2]; /* OP_OpenWrite cursors for the ONEPASS opt */ WhereMaskSet sMaskSet; /* Map cursor numbers to bitmasks */ WhereClause sWC; /* Decomposition of the WHERE clause */ WhereLevel a[1]; /* Information about each nest loop in WHERE */ }; | > | 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 | u8 okOnePass; /* Ok to use one-pass algorithm for UPDATE/DELETE */ u8 untestedTerms; /* Not all WHERE terms resolved by outer loop */ u8 eDistinct; /* One of the WHERE_DISTINCT_* values below */ u8 nLevel; /* Number of nested loop */ int iTop; /* The very beginning of the WHERE loop */ int iContinue; /* Jump here to continue with next record */ int iBreak; /* Jump here to break out of the loop */ int iExplain; /* Address of OP_Explain (if WHERE_ONETABLE_ONLY) */ int savedNQueryLoop; /* pParse->nQueryLoop outside the WHERE loop */ int aiCurOnePass[2]; /* OP_OpenWrite cursors for the ONEPASS opt */ WhereMaskSet sMaskSet; /* Map cursor numbers to bitmasks */ WhereClause sWC; /* Decomposition of the WHERE clause */ WhereLevel a[1]; /* Information about each nest loop in WHERE */ }; |
︙ | ︙ |
Changes to test/scanstatus.test.
︙ | ︙ | |||
49 50 51 52 53 54 55 | SELECT count(*) FROM t1, t2; } 6 do_scanstatus_test 1.4 { nLoop 1 nVisit 2 nEst 2 zName t1 zExplain {SCAN TABLE t1} nLoop 2 nVisit 6 nEst 3 zName t2 zExplain {SCAN TABLE t2} } | | < > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 | SELECT count(*) FROM t1, t2; } 6 do_scanstatus_test 1.4 { nLoop 1 nVisit 2 nEst 2 zName t1 zExplain {SCAN TABLE t1} nLoop 2 nVisit 6 nEst 3 zName t2 zExplain {SCAN TABLE t2} } do_execsql_test 1.5 { ANALYZE } do_execsql_test 1.6 { SELECT count(*) FROM t1, t2 WHERE t2.rowid>1; } 4 do_scanstatus_test 1.7 { nLoop 1 nVisit 2 nEst 2 zName t2 zExplain {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid>?)} nLoop 2 nVisit 4 nEst 2 zName t1 zExplain {SCAN TABLE t1} } do_execsql_test 1.8 { SELECT count(*) FROM t1, t2 WHERE t2.rowid>1; } 4 do_scanstatus_test 1.9 { nLoop 2 nVisit 4 nEst 2 zName t2 zExplain {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid>?)} nLoop 4 nVisit 8 nEst 2 zName t1 zExplain {SCAN TABLE t1} } do_test 1.9 { sqlite3_stmt_scanstatus_reset [db_last_stmt_ptr db] } {} do_scanstatus_test 1.10 { nLoop 0 nVisit 0 nEst 2 zName t2 zExplain {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid>?)} nLoop 0 nVisit 0 nEst 2 zName t1 zExplain {SCAN TABLE t1} } #------------------------------------------------------------------------- # Try a few different types of scans. # reset_db do_execsql_test 2.1 { CREATE TABLE x1(i INTEGER PRIMARY KEY, j); INSERT INTO x1 VALUES(1, 'one'); INSERT INTO x1 VALUES(2, 'two'); INSERT INTO x1 VALUES(3, 'three'); INSERT INTO x1 VALUES(4, 'four'); CREATE INDEX x1j ON x1(j); SELECT * FROM x1 WHERE i=2; } {2 two} do_scanstatus_test 2.2 { nLoop 1 nVisit 1 nEst 1 zName x1 zExplain {SEARCH TABLE x1 USING INTEGER PRIMARY KEY (rowid=?)} } do_execsql_test 2.3.1 { SELECT * FROM x1 WHERE j='two' } {2 two} do_scanstatus_test 2.3.2 { nLoop 1 nVisit 1 nEst 10 zName x1j zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j=?)} } do_execsql_test 2.4.1 { SELECT * FROM x1 WHERE j<'two' } {4 four 1 one 3 three} do_scanstatus_test 2.4.2 { nLoop 1 nVisit 4 nEst 262144 zName x1j zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j<?)} } do_execsql_test 2.5.1 { SELECT * FROM x1 WHERE j>='two' } {2 two} do_scanstatus_test 2.5.2 { nLoop 1 nVisit 1 nEst 262144 zName x1j zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j>?)} } do_execsql_test 2.6.1 { SELECT * FROM x1 WHERE j BETWEEN 'three' AND 'two' } {3 three 2 two} do_scanstatus_test 2.6.2 { nLoop 1 nVisit 2 nEst 16384 zName x1j zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j>? AND j<?)} } do_execsql_test 2.7.1 { CREATE TABLE x2(i INTEGER, j, k); INSERT INTO x2 SELECT i, j, i || ' ' || j FROM x1; CREATE INDEX x2j ON x2(j); CREATE INDEX x2ij ON x2(i, j); SELECT * FROM x2 WHERE j BETWEEN 'three' AND 'two' } {3 three {3 three} 2 two {2 two}} do_scanstatus_test 2.7.2 { nLoop 1 nVisit 2 nEst 16384 zName x2j zExplain {SEARCH TABLE x2 USING INDEX x2j (j>? AND j<?)} } do_execsql_test 2.8.1 { SELECT * FROM x2 WHERE i=1 AND j='two' } do_scanstatus_test 2.8.2 { nLoop 1 nVisit 1 nEst 8 zName x2ij zExplain {SEARCH TABLE x2 USING INDEX x2ij (i=? AND j=?)} } do_execsql_test 2.9.1 { SELECT * FROM x2 WHERE i=5 AND j='two' } do_scanstatus_test 2.9.2 { nLoop 1 nVisit 0 nEst 8 zName x2ij zExplain {SEARCH TABLE x2 USING INDEX x2ij (i=? AND j=?)} } do_execsql_test 2.10.1 { SELECT * FROM x2 WHERE i=3 AND j='three' } {3 three {3 three}} do_scanstatus_test 2.10.2 { nLoop 1 nVisit 2 nEst 8 zName x2ij zExplain {SEARCH TABLE x2 USING INDEX x2ij (i=? AND j=?)} } #------------------------------------------------------------------------- # Try with queries that use the OR optimization. # do_execsql_test 3.1 { CREATE TABLE a1(a, b, c, d); CREATE INDEX a1a ON a1(a); CREATE INDEX a1bc ON a1(b, c); WITH d(x) AS (SELECT 1 UNION ALL SELECT x+1 AS n FROM d WHERE n<=100) INSERT INTO a1 SELECT x, x, x, x FROM d; } do_execsql_test 3.2.1 { SELECT d FROM a1 WHERE (a=4 OR b=13) } {4 13} do_scanstatus_test 2.4 { nLoop 1 nVisit 2 nEst 10 zName a1a zExplain {SEARCH TABLE a1 USING INDEX a1a (a=?)} nLoop 1 nVisit 2 nEst 10 zName a1bc zExplain {SEARCH TABLE a1 USING INDEX a1bc (b=?)} } finish_test |