Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Test cases. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | multikey-opt-idea |
Files: | files | file ages | folders |
SHA3-256: |
085e863713a3f2d420c0076b275a6ac4 |
User & Date: | drh 2018-06-07 16:07:00.142 |
Context
2018-06-07
| ||
17:32 | Remove the NextIfOpen and PrevIfOpen opcodes which are no longer needed when the IN-early-out optimization is working. (check-in: 439c816227 user: drh tags: multikey-opt-idea) | |
16:07 | Test cases. (check-in: 085e863713 user: drh tags: multikey-opt-idea) | |
15:28 | Merge the ".stat/.eqp" CLI fix from trunk. (check-in: a91cad3381 user: drh tags: multikey-opt-idea) | |
Changes
Changes to src/wherecode.c.
︙ | ︙ | |||
588 589 590 591 592 593 594 | int iCol = aiMap ? aiMap[iMap++] : 0; pIn->addrInTop = sqlite3VdbeAddOp3(v,OP_Column,iTab, iCol, iOut); } sqlite3VdbeAddOp1(v, OP_IsNull, iOut); VdbeCoverage(v); if( i==iEq ){ pIn->iCur = iTab; pIn->eEndLoopOp = bRev ? OP_PrevIfOpen : OP_NextIfOpen; | | | 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 | int iCol = aiMap ? aiMap[iMap++] : 0; pIn->addrInTop = sqlite3VdbeAddOp3(v,OP_Column,iTab, iCol, iOut); } sqlite3VdbeAddOp1(v, OP_IsNull, iOut); VdbeCoverage(v); if( i==iEq ){ pIn->iCur = iTab; pIn->eEndLoopOp = bRev ? OP_PrevIfOpen : OP_NextIfOpen; 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{ |
︙ | ︙ |
Added test/in6.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 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 | # 2018-06-07 # # 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. # #*********************************************************************** # # A multi-key index that uses an IN operator on one of the keys other # than the left-most key is able to abort the IN-operator loop early # if key terms further to the left do not match. # # Call this the "multikey-IN-operator early-out optimization" or # just "IN-early-out" optimization for short. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix in6 do_test in6-1.1 { db eval { CREATE TABLE t1(a,b,c,d); WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100) INSERT INTO t1(a,b,c,d) SELECT 100, 200+x/2, 300+x/5, x FROM c; CREATE INDEX t1abc ON t1(a,b,c); } set ::sqlite_search_count 0 db eval { SELECT d FROM t1 WHERE a=99 AND b IN (200,205,201,204) AND c IN (304,302,309,308); } } {} do_test in6-1.2 { set ::sqlite_search_count } {0} ;# Without the IN-early-out optimization, this value would be 15 # The multikey-IN-operator early-out optimization does not apply # when the IN operator is on the left-most column of the index. # do_test in6-1.3 { db eval { EXPLAIN SELECT d FROM t1 WHERE a IN (98,99,100,101) AND b=200 AND c=300; } } {~/(IfNoHope|SeekHit)/} set sqlite_search_count 0 do_execsql_test in6-1.4 { SELECT d FROM t1 WHERE a=100 AND b IN (200,201,202,204) AND c IN (300,302,301,305) ORDER BY +d; } {1 2 3 4 5 8 9} do_test in6-1.5 { set ::sqlite_search_count } {39} do_execsql_test in6-2.1 { CREATE TABLE t2(e INT UNIQUE, f TEXT); SELECT d, f FROM t1 LEFT JOIN t2 ON (e=d) WHERE a=100 AND b IN (200,201,202,204) AND c IN (300,302,301,305) ORDER BY +d; } {1 {} 2 {} 3 {} 4 {} 5 {} 8 {} 9 {}} finish_test |
Changes to test/where.test.
︙ | ︙ | |||
486 487 488 489 490 491 492 | SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1; } } {2 1 9 3 1 16 6} do_test where-5.14 { count { SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1; } | | | | 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 | SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1; } } {2 1 9 3 1 16 6} do_test where-5.14 { count { SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1; } } {2 1 9 4} do_test where-5.15 { count { SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,16) ORDER BY 1; } } {2 1 9 3 1 16 8} do_test where-5.100 { db eval { SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969) ORDER BY x, y } } {2 1 9 54 5 3025 62 5 3969} do_test where-5.101 { |
︙ | ︙ |