The IN-early-out optimization: When doing a look-up on a multi-column index

and an IN operator is used on a column other than the left-most column, then
if no rows match against the first IN value, check to make sure there exist
rows that match the columns to the right before continuing with the next IN
value.

FossilOrigin-Name: 09fffbdf9f2f6ce31a22d5a6df7a45f19a16628da622f12d6e33171cce09fb21

1 files changed, 15 insertions, 1 deletions

diff --git a/src/wherecode.c b/src/wherecode.c
index c9edab7b0..67f5e3073 100644
--- a/src/wherecode.c
+++ b/src/wherecode.c
@@ -591,7 +591,14 @@ static int codeEqualityTerm(
           sqlite3VdbeAddOp1(v, OP_IsNull, iOut); VdbeCoverage(v);
           if( i==iEq ){
             pIn->iCur = iTab;
-            pIn->eEndLoopOp = bRev ? OP_PrevIfOpen : OP_NextIfOpen;
+            pIn->eEndLoopOp = bRev ? OP_Prev : OP_Next;
+            if( iEq>0 && (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0 ){
+              pIn->iBase = iReg - i;
+              pIn->nPrefix = i;
+              pLoop->wsFlags |= WHERE_IN_EARLYOUT;
+            }else{
+              pIn->nPrefix = 0;
+            }
           }else{
             pIn->eEndLoopOp = OP_Noop;
           }
@@ -1658,6 +1665,9 @@ Bitmask sqlite3WhereCodeOneLoopStart(
       ** above has already left the cursor sitting on the correct row,
       ** so no further seeking is needed */
     }else{
+      if( pLoop->wsFlags & WHERE_IN_EARLYOUT ){
+        sqlite3VdbeAddOp1(v, OP_SeekHit, iIdxCur);
+      }
       op = aStartOp[(start_constraints<<2) + (startEq<<1) + bRev];
       assert( op!=0 );
       sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint);
@@ -1721,6 +1731,10 @@ Bitmask sqlite3WhereCodeOneLoopStart(
       testcase( op==OP_IdxLE );  VdbeCoverageIf(v, op==OP_IdxLE );
     }
 
+    if( pLoop->wsFlags & WHERE_IN_EARLYOUT ){
+      sqlite3VdbeAddOp2(v, OP_SeekHit, iIdxCur, 1);
+    }
+
     /* Seek the table cursor, if required */
     if( omitTable ){
       /* pIdx is a covering index.  No need to access the main table. */