Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Optimize range constraints on the rowid column of fts3/4 tables even if there is no MATCH clause in the query. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
85dc12625d300fe48f3c096f54ebcb8b |
| User & Date: | dan 2015-01-29 11:52:22 |
Context
|
2015-01-29
| ||
| 18:38 | Split up the SRC variable in Makefile.msc to avoid over-long cmd.exe commands when TOP is set to a long pathname. (check-in: 7d70ac65 user: drh tags: trunk) | |
| 17:54 | Add the INITMODE test-control. (check-in: 5940af8e user: drh tags: initmode-testctrl) | |
| 11:52 | Optimize range constraints on the rowid column of fts3/4 tables even if there is no MATCH clause in the query. (check-in: 85dc1262 user: dan tags: trunk) | |
|
2015-01-27
| ||
| 21:24 | Fix harmless compiler warnings. (check-in: e7d2ec04 user: mistachkin tags: trunk) | |
Changes
Changes to ext/fts3/fts3.c.
3160 3160 3161 3161 /* Compile a SELECT statement for this cursor. For a full-table-scan, the 3162 3162 ** statement loops through all rows of the %_content table. For a 3163 3163 ** full-text query or docid lookup, the statement retrieves a single 3164 3164 ** row by docid. 3165 3165 */ 3166 3166 if( eSearch==FTS3_FULLSCAN_SEARCH ){ 3167 - zSql = sqlite3_mprintf( 3168 - "SELECT %s ORDER BY rowid %s", 3169 - p->zReadExprlist, (pCsr->bDesc ? "DESC" : "ASC") 3170 - ); 3167 + if( pDocidGe || pDocidLe ){ 3168 + zSql = sqlite3_mprintf( 3169 + "SELECT %s WHERE rowid BETWEEN %lld AND %lld ORDER BY rowid %s", 3170 + p->zReadExprlist, pCsr->iMinDocid, pCsr->iMaxDocid, 3171 + (pCsr->bDesc ? "DESC" : "ASC") 3172 + ); 3173 + }else{ 3174 + zSql = sqlite3_mprintf("SELECT %s ORDER BY rowid %s", 3175 + p->zReadExprlist, (pCsr->bDesc ? "DESC" : "ASC") 3176 + ); 3177 + } 3171 3178 if( zSql ){ 3172 3179 rc = sqlite3_prepare_v2(p->db, zSql, -1, &pCsr->pStmt, 0); 3173 3180 sqlite3_free(zSql); 3174 3181 }else{ 3175 3182 rc = SQLITE_NOMEM; 3176 3183 } 3177 3184 }else if( eSearch==FTS3_DOCID_SEARCH ){
Changes to test/fts3query.test.
204 204 6 "SELECT snippet(t3, 'XXX', 'YYY', 'ZZZ', 0) FROM t3 WHERE t3 MATCH 'gestures'" 205 205 {{no XXXgesturesYYY}} 206 206 207 207 7 "SELECT snippet(t3, 'XXX', 'YYY', 'ZZZ', 1, 5) FROM t3 WHERE t3 MATCH 'gestures'" 208 208 {{ZZZthe hand XXXgesturesYYY (called beatsZZZ}} 209 209 } 210 210 211 +# Test some range queries on the rowid field. 212 +# 213 +do_execsql_test 7.1 { 214 + CREATE VIRTUAL TABLE ft4 USING fts4(x); 215 + CREATE TABLE t4(x); 216 +} 217 + 218 +set SMALLINT -9223372036854775808 219 +set LARGEINT 9223372036854775807 220 +do_test 7.2 { 221 + db transaction { 222 + foreach {iFirst nEntry} [subst { 223 + 0 100 224 + $SMALLINT 100 225 + [expr $LARGEINT - 99] 100 226 + }] { 227 + for {set i 0} {$i < $nEntry} {incr i} { 228 + set iRowid [expr $i + $iFirst] 229 + execsql { 230 + INSERT INTO ft4(rowid, x) VALUES($iRowid, 'x y z'); 231 + INSERT INTO t4(rowid, x) VALUES($iRowid, 'x y z'); 232 + } 233 + } 234 + } 235 + } 236 +} {} 237 + 238 +foreach {tn iFirst iLast} [subst { 239 + 1 5 10 240 + 2 $SMALLINT [expr $SMALLINT+5] 241 + 3 $SMALLINT [expr $SMALLINT+50] 242 + 4 [expr $LARGEINT-5] $LARGEINT 243 + 5 $LARGEINT $LARGEINT 244 + 6 $SMALLINT $LARGEINT 245 + 7 $SMALLINT $SMALLINT 246 + 8 $LARGEINT $SMALLINT 247 +}] { 248 + set res [db eval { 249 + SELECT rowid FROM t4 WHERE rowid BETWEEN $iFirst AND $iLast 250 + } ] 251 + 252 + do_execsql_test 7.2.$tn.1.[llength $res] { 253 + SELECT rowid FROM ft4 WHERE rowid BETWEEN $iFirst AND $iLast 254 + } $res 255 + do_execsql_test 7.2.$tn.2.[llength $res] { 256 + SELECT rowid FROM ft4 WHERE rowid BETWEEN $iFirst AND $iLast 257 + ORDER BY rowid DESC 258 + } [lsort -decr -integer $res] 259 +} 260 + 261 +foreach ii [db eval {SELECT rowid FROM t4}] { 262 + set res1 [db eval {SELECT rowid FROM t4 WHERE rowid > $ii}] 263 + set res2 [db eval {SELECT rowid FROM t4 WHERE rowid < $ii}] 264 + 265 + do_execsql_test 7.3.$ii.1 { 266 + SELECT rowid FROM ft4 WHERE rowid > $ii 267 + } $res1 268 + 269 + do_execsql_test 7.3.$ii.2 { 270 + SELECT rowid FROM ft4 WHERE rowid < $ii 271 + } $res2 272 + 273 + do_execsql_test 7.3.$ii.3 { 274 + SELECT rowid FROM ft4 WHERE rowid > $ii ORDER BY rowid DESC 275 + } [lsort -integer -decr $res1] 276 + 277 + do_execsql_test 7.3.$ii.4 { 278 + SELECT rowid FROM ft4 WHERE rowid < $ii ORDER BY rowid DESC 279 + } [lsort -integer -decr $res2] 280 +} 211 281 212 282 finish_test 283 + 284 +