/ Check-in [85dc1262]
Login

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: 85dc12625d300fe48f3c096f54ebcb8b6ef4e30a
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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  +