Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1391,10 +1391,11 @@ u8 vtabOnConflict; /* Value to return for s3_vtab_on_conflict() */ u8 isTransactionSavepoint; /* True if the outermost savepoint is a TS */ u8 mTrace; /* zero or more SQLITE_TRACE flags */ u8 noSharedCache; /* True if no shared-cache backends */ u8 nSqlExec; /* Number of pending OP_SqlExec opcodes */ + LogEst iSortCost; /* Extra cost applied to sorting */ int nextPagesize; /* Pagesize after VACUUM if >0 */ u32 magic; /* Magic number for detect library misuse */ int nChange; /* Value returned by sqlite3_changes() */ int nTotalChange; /* Value returned by sqlite3_total_changes() */ int aLimit[SQLITE_N_LIMIT]; /* Limits */ @@ -1559,11 +1560,11 @@ ** Bits of the sqlite3.dbOptFlags field that are used by the ** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to ** selectively disable various optimizations. */ #define SQLITE_QueryFlattener 0x0001 /* Query flattening */ - /* 0x0002 available for reuse */ +#define SQLITE_PropagateConst 0x0002 /* The constant propagation opt */ #define SQLITE_GroupByOrder 0x0004 /* GROUPBY cover of ORDERBY */ #define SQLITE_FactorOutConst 0x0008 /* Constant factoring */ #define SQLITE_DistinctOpt 0x0010 /* DISTINCT using indexes */ #define SQLITE_CoverIdxScan 0x0020 /* Covering index scans */ #define SQLITE_OrderByIdxJoin 0x0040 /* ORDER BY of joins via index */ @@ -1574,11 +1575,11 @@ #define SQLITE_Stat34 0x0800 /* Use STAT3 or STAT4 data */ /* TH3 expects the Stat34 ^^^^^^ value to be 0x0800. Don't change it */ #define SQLITE_PushDown 0x1000 /* The push-down optimization */ #define SQLITE_SimplifyJoin 0x2000 /* Convert LEFT JOIN to JOIN */ #define SQLITE_SkipScan 0x4000 /* Skip-scans */ -#define SQLITE_PropagateConst 0x8000 /* The constant propagation opt */ +#define SQLITE_SortIfFaster 0x8000 /* ORDER BY using sorter if faster */ #define SQLITE_AllOpts 0xffff /* All optimizations */ /* ** Macros for testing whether or not optimizations are enabled or disabled. */ Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -4107,15 +4107,17 @@ if( aSortCost[isOrdered]==0 ){ aSortCost[isOrdered] = whereSortingCost( pWInfo, nRowEst, nOrderBy, isOrdered ); } - /* TUNING: Add a small extra penalty (5) to sorting as an - ** extra encouragment to the query planner to select a plan - ** where the rows emerge in the correct order without any sorting - ** required. */ - rCost = sqlite3LogEstAdd(rUnsorted, aSortCost[isOrdered]) + 5; + rCost = sqlite3LogEstAdd(rUnsorted, aSortCost[isOrdered]); + if( OptimizationDisabled(db, SQLITE_SortIfFaster) ){ + /* if the SortIfFaster optimization is disabled, then set the + ** sort cost very high, to encourage the query to return the + ** results in natural order, if at all possible */ + rCost += 200; + } WHERETRACE(0x002, ("---- sort cost=%-3d (%d/%d) increases cost %3d to %-3d\n", aSortCost[isOrdered], (nOrderBy-isOrdered), nOrderBy, rUnsorted, rCost)); Index: test/where.test ================================================================== --- test/where.test +++ test/where.test @@ -580,11 +580,11 @@ } {/1 100 4 2 99 9 3 98 16 .* nosort/} do_test where-6.7.2 { cksort { SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 1 } -} {1 100 4 nosort} +} {1 100 4 sort} ifcapable subquery { do_test where-6.8a { cksort { SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3 }