Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Multi-index OR optimizer response to ORDER BY rowid. But fix in sqlite3_stmt_status(): report a full table scan when "ORDER BY rowid" is used without constraints. (CVS 6069) |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
3464d369d3b6899ec726cf5b42b68b1d |
| User & Date: | drh 2008-12-28 20:47:03 |
Context
|
2008-12-29
| ||
| 10:39 | Set the OPFLG_IN1 flag on a couple of extra opcodes. (CVS 6070) check-in: 03d03504 user: danielk1977 tags: trunk | |
|
2008-12-28
| ||
| 20:47 | Multi-index OR optimizer response to ORDER BY rowid. But fix in sqlite3_stmt_status(): report a full table scan when "ORDER BY rowid" is used without constraints. (CVS 6069) check-in: 3464d369 user: drh tags: trunk | |
| 18:35 | Optimize WHERE clauses that constain AND, BETWEEN, and LIKE terms as operands of an OR. (CVS 6068) check-in: 67cf24b3 user: drh tags: trunk | |
Changes
Changes to src/where.c.
12 12 ** This module contains C code that generates VDBE code used to process 13 13 ** the WHERE clause of SQL statements. This module is responsible for 14 14 ** generating the code that loops through a table looking for applicable 15 15 ** rows. Indices are selected and used to speed the search when doing 16 16 ** so is applicable. Because this module is responsible for selecting 17 17 ** indices, you might also think of this module as the "query optimizer". 18 18 ** 19 -** $Id: where.c,v 1.346 2008/12/28 18:35:09 drh Exp $ 19 +** $Id: where.c,v 1.347 2008/12/28 20:47:03 drh Exp $ 20 20 */ 21 21 #include "sqliteInt.h" 22 22 23 23 /* 24 24 ** Trace output macros 25 25 */ 26 26 #if defined(SQLITE_TEST) || defined(SQLITE_DEBUG) ................................................................................ 1873 1873 nRow += sTermCost.nRow; 1874 1874 } 1875 1875 if( rTotal<pCost->rCost ){ 1876 1876 pCost->rCost = rTotal; 1877 1877 pCost->nRow = nRow; 1878 1878 pCost->plan.wsFlags = WHERE_MULTI_OR; 1879 1879 pCost->plan.u.pTerm = pTerm; 1880 + if( pOrderBy!=0 1881 + && sortableByRowid(iCur, pOrderBy, pWC->pMaskSet, &rev) 1882 + && !rev 1883 + ){ 1884 + pCost->plan.wsFlags = WHERE_ORDERBY|WHERE_MULTI_OR; 1885 + } 1880 1886 } 1881 1887 } 1882 1888 } 1883 1889 #endif /* SQLITE_OMIT_OR_OPTIMIZATION */ 1884 1890 1885 1891 /* If the pSrc table is the right table of a LEFT JOIN then we may not 1886 1892 ** use an index to satisfy IS NULL constraints on that table. This is ................................................................................ 2422 2428 } 2423 2429 disableTerm(pLevel, pEnd); 2424 2430 } 2425 2431 start = sqlite3VdbeCurrentAddr(v); 2426 2432 pLevel->op = bRev ? OP_Prev : OP_Next; 2427 2433 pLevel->p1 = iCur; 2428 2434 pLevel->p2 = start; 2435 + pLevel->p5 = (pStart==0 && pEnd==0) ?1:0; 2429 2436 codeRowSetEarly = regRowSet>=0 ? whereRowReadyForOutput(pWC) : 0; 2430 2437 if( codeRowSetEarly || testOp!=OP_Noop ){ 2431 2438 int r1 = sqlite3GetTempReg(pParse); 2432 2439 sqlite3VdbeAddOp2(v, OP_Rowid, iCur, r1); 2433 2440 if( testOp!=OP_Noop ){ 2434 2441 sqlite3VdbeAddOp3(v, testOp, memEndValue, addrBrk, r1); 2435 2442 sqlite3VdbeChangeP5(v, SQLITE_AFF_NUMERIC | SQLITE_JUMPIFNULL);
Changes to test/where7.test.
7 7 # May you find forgiveness for yourself and forgive others. 8 8 # May you share freely, never taking more than you give. 9 9 # 10 10 #*********************************************************************** 11 11 # This file implements regression tests for SQLite library. The 12 12 # focus of this file is testing the multi-index OR clause optimizer. 13 13 # 14 -# $Id: where7.test,v 1.2 2008/12/28 18:35:09 drh Exp $ 14 +# $Id: where7.test,v 1.3 2008/12/28 20:47:03 drh Exp $ 15 15 16 16 set testdir [file dirname $argv0] 17 17 source $testdir/tester.tcl 18 18 19 19 ifcapable !or_opt { 20 20 finish_test 21 21 return ................................................................................ 22 22 } 23 23 24 24 # Evaluate SQL. Return the result set followed by the 25 25 # and the number of full-scan steps. 26 26 # 27 27 proc count_steps {sql} { 28 28 set r [db eval $sql] 29 - lappend r scan [db status step] 29 + lappend r scan [db status step] sort [db status sort] 30 30 } 31 31 32 32 # Build some test data 33 33 # 34 34 do_test where7-1.1 { 35 35 execsql { 36 36 CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d); ................................................................................ 42 42 CREATE INDEX t1b ON t1(b); 43 43 CREATE INDEX t1c ON t1(c); 44 44 SELECT * FROM t1; 45 45 } 46 46 } {1 2 3 4 2 3 4 5 3 4 6 8 4 5 10 15 5 10 100 1000} 47 47 do_test where7-1.2 { 48 48 count_steps { 49 - SELECT a FROM t1 WHERE b=3 OR c=6 49 + SELECT a FROM t1 WHERE b=3 OR c=6 ORDER BY a 50 50 } 51 -} {2 3 scan 0} 51 +} {2 3 scan 0 sort 0} 52 52 do_test where7-1.3 { 53 53 count_steps { 54 - SELECT a FROM t1 WHERE b=3 OR +c=6 54 + SELECT a FROM t1 WHERE b=3 OR +c=6 ORDER BY a 55 55 } 56 -} {2 3 scan 4} 56 +} {2 3 scan 4 sort 0} 57 57 do_test where7-1.4 { 58 58 count_steps { 59 - SELECT a FROM t1 WHERE +b=3 OR c=6 59 + SELECT a FROM t1 WHERE +b=3 OR c=6 ORDER BY 1 60 60 } 61 -} {2 3 scan 4} 61 +} {2 3 scan 4 sort 0} 62 62 do_test where7-1.5 { 63 63 count_steps { 64 - SELECT a FROM t1 WHERE 3=b OR c=6 64 + SELECT a FROM t1 WHERE 3=b OR c=6 ORDER BY rowid 65 65 } 66 -} {2 3 scan 0} 66 +} {2 3 scan 0 sort 0} 67 67 do_test where7-1.6 { 68 68 count_steps { 69 - SELECT a FROM t1 WHERE (3=b OR c=6) AND +a>0 69 + SELECT a FROM t1 WHERE (3=b OR c=6) AND +a>0 ORDER BY a 70 70 } 71 -} {2 3 scan 0} 71 +} {2 3 scan 0 sort 0} 72 72 do_test where7-1.7 { 73 73 count_steps { 74 - SELECT a FROM t1 WHERE (b=3 OR c>10) 74 + SELECT a FROM t1 WHERE (b=3 OR c>10) ORDER BY a 75 75 } 76 -} {2 5 scan 0} 76 +} {2 5 scan 0 sort 0} 77 77 do_test where7-1.8 { 78 78 count_steps { 79 - SELECT a FROM t1 WHERE (b=3 OR c>=10) 79 + SELECT a FROM t1 WHERE (b=3 OR c>=10) ORDER BY a 80 80 } 81 -} {2 4 5 scan 0} 81 +} {2 4 5 scan 0 sort 0} 82 82 do_test where7-1.9 { 83 83 count_steps { 84 - SELECT a FROM t1 WHERE (b=3 OR c>=10 OR c=4) 84 + SELECT a FROM t1 WHERE (b=3 OR c>=10 OR c=4) ORDER BY a 85 85 } 86 -} {2 4 5 scan 0} 86 +} {2 4 5 scan 0 sort 0} 87 87 do_test where7-1.10 { 88 88 count_steps { 89 - SELECT a FROM t1 WHERE (b=3 OR c>=10 OR c=4 OR b>10) 89 + SELECT a FROM t1 WHERE (b=3 OR c>=10 OR c=4 OR b>10) ORDER BY a 90 90 } 91 -} {2 4 5 scan 0} 91 +} {2 4 5 scan 0 sort 0} 92 92 do_test where7-1.11 { 93 93 count_steps { 94 - SELECT a FROM t1 WHERE (d=5 AND b=3) OR c==100; 94 + SELECT a FROM t1 WHERE (d=5 AND b=3) OR c==100 ORDER BY a; 95 95 } 96 -} {2 5 scan 0} 96 +} {2 5 scan 0 sort 0} 97 97 do_test where7-1.12 { 98 98 breakpoint 99 99 count_steps { 100 - SELECT a FROM t1 WHERE (b BETWEEN 2 AND 4) OR c=100 100 + SELECT a FROM t1 WHERE (b BETWEEN 2 AND 4) OR c=100 ORDER BY a 101 + } 102 +} {1 2 3 5 scan 0 sort 0} 103 +do_test where7-1.13 { 104 +breakpoint 105 + count_steps { 106 + SELECT a FROM t1 WHERE (b BETWEEN 0 AND 2) OR (c BETWEEN 9 AND 999) 107 + ORDER BY a DESC 101 108 } 102 -} {1 2 3 5 scan 0} 109 +} {5 4 1 scan 0 sort 1} 103 110 104 111 105 112 finish_test