/ Check-in [a0ba55ff]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Update the spellfix virtual table to optimize queries of the form "SELECT ... FROM tbl WHERE rowid=?".
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a0ba55ff0596c5f15e9cdb254c68ef50df2dfaad
User & Date: dan 2014-01-22 17:43:16
Context
2014-01-22
18:16
Change the WITH RECURSIVE implementation to use a queue instead of a pair of tables. Add support for ORDER BY, LIMIT, and OFFSET on recursive queries. check-in: b6cea420 user: drh tags: trunk
17:43
Update the spellfix virtual table to optimize queries of the form "SELECT ... FROM tbl WHERE rowid=?". check-in: a0ba55ff user: dan tags: trunk
2014-01-21
15:04
Remove the undocumented requirement for applications that use an SQLITE_ENABLE_SQLLOG build to define a sqlite3_init_sqllog() function. check-in: 5e43bf01 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/misc/spellfix.c.

  2047   2047   **
  2048   2048   **   (A)    word MATCH $str
  2049   2049   **   (B)    langid == $langid
  2050   2050   **   (C)    top = $top
  2051   2051   **   (D)    scope = $scope
  2052   2052   **   (E)    distance < $distance
  2053   2053   **   (F)    distance <= $distance
         2054  +**   (G)    rowid = $rowid
  2054   2055   **
  2055   2056   ** The plan number is a bit mask formed with these bits:
  2056   2057   **
  2057   2058   **   0x01   (A) is found
  2058   2059   **   0x02   (B) is found
  2059   2060   **   0x04   (C) is found
  2060   2061   **   0x08   (D) is found
  2061   2062   **   0x10   (E) is found
  2062   2063   **   0x20   (F) is found
         2064  +**   0x40   (G) is found
  2063   2065   **
  2064         -** filter.argv[*] values contains $str, $langid, $top, and $scope,
         2066  +** filter.argv[*] values contains $str, $langid, $top, $scope and $rowid
  2065   2067   ** if specified and in that order.
  2066   2068   */
  2067   2069   static int spellfix1BestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
  2068   2070     int iPlan = 0;
  2069   2071     int iLangTerm = -1;
  2070   2072     int iTopTerm = -1;
  2071   2073     int iScopeTerm = -1;
  2072   2074     int iDistTerm = -1;
         2075  +  int iRowidTerm = -1;
  2073   2076     int i;
  2074   2077     const struct sqlite3_index_constraint *pConstraint;
  2075   2078     pConstraint = pIdxInfo->aConstraint;
  2076   2079     for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){
  2077   2080       if( pConstraint->usable==0 ) continue;
  2078   2081   
  2079   2082       /* Terms of the form:  word MATCH $str */
................................................................................
  2118   2121        && pConstraint->iColumn==SPELLFIX_COL_DISTANCE
  2119   2122        && (pConstraint->op==SQLITE_INDEX_CONSTRAINT_LT
  2120   2123             || pConstraint->op==SQLITE_INDEX_CONSTRAINT_LE)
  2121   2124       ){
  2122   2125         iPlan |= pConstraint->op==SQLITE_INDEX_CONSTRAINT_LT ? 16 : 32;
  2123   2126         iDistTerm = i;
  2124   2127       }
         2128  +
         2129  +    /* Terms of the form:  distance < $dist or distance <= $dist */
         2130  +    if( (iPlan & 64)==0
         2131  +     && pConstraint->iColumn<0
         2132  +     && pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ
         2133  +    ){
         2134  +      iPlan |= 64;
         2135  +      iRowidTerm = i;
         2136  +    }
  2125   2137     }
  2126   2138     if( iPlan&1 ){
  2127   2139       int idx = 2;
  2128   2140       pIdxInfo->idxNum = iPlan;
  2129   2141       if( pIdxInfo->nOrderBy==1
  2130   2142        && pIdxInfo->aOrderBy[0].iColumn==SPELLFIX_COL_SCORE
  2131   2143        && pIdxInfo->aOrderBy[0].desc==0
................................................................................
  2145   2157         pIdxInfo->aConstraintUsage[iScopeTerm].omit = 1;
  2146   2158       }
  2147   2159       if( iPlan&(16|32) ){
  2148   2160         pIdxInfo->aConstraintUsage[iDistTerm].argvIndex = idx++;
  2149   2161         pIdxInfo->aConstraintUsage[iDistTerm].omit = 1;
  2150   2162       }
  2151   2163       pIdxInfo->estimatedCost = 1e5;
         2164  +  }else if( (iPlan & 64) ){
         2165  +    pIdxInfo->idxNum = 64;
         2166  +    pIdxInfo->aConstraintUsage[iRowidTerm].argvIndex = 1;
         2167  +    pIdxInfo->aConstraintUsage[iRowidTerm].omit = 1;
         2168  +    pIdxInfo->estimatedCost = 5;
  2152   2169     }else{
  2153   2170       pIdxInfo->idxNum = 0;
  2154   2171       pIdxInfo->estimatedCost = 1e50;
  2155   2172     }
  2156   2173     return SQLITE_OK;
  2157   2174   }
  2158   2175   
................................................................................
  2461   2478   */
  2462   2479   static int spellfix1FilterForFullScan(
  2463   2480     spellfix1_cursor *pCur,
  2464   2481     int idxNum,
  2465   2482     int argc,
  2466   2483     sqlite3_value **argv
  2467   2484   ){
  2468         -  int rc;
         2485  +  int rc = SQLITE_OK;
  2469   2486     char *zSql;
  2470   2487     spellfix1_vtab *pVTab = pCur->pVTab;
  2471   2488     spellfix1ResetCursor(pCur);
         2489  +  assert( idxNum==0 || idxNum==64 );
  2472   2490     zSql = sqlite3_mprintf(
  2473         -     "SELECT word, rank, NULL, langid, id FROM \"%w\".\"%w_vocab\"",
  2474         -     pVTab->zDbName, pVTab->zTableName);
         2491  +     "SELECT word, rank, NULL, langid, id FROM \"%w\".\"%w_vocab\"%s",
         2492  +     pVTab->zDbName, pVTab->zTableName,
         2493  +     ((idxNum & 64) ? " WHERE rowid=?" : "")
         2494  +  );
  2475   2495     if( zSql==0 ) return SQLITE_NOMEM;
  2476   2496     rc = sqlite3_prepare_v2(pVTab->db, zSql, -1, &pCur->pFullScan, 0);
  2477   2497     sqlite3_free(zSql);
         2498  +  if( rc==SQLITE_OK && (idxNum & 64) ){
         2499  +    assert( argc==1 );
         2500  +    rc = sqlite3_bind_value(pCur->pFullScan, 1, argv[0]);
         2501  +  }
  2478   2502     pCur->nRow = pCur->iRow = 0;
  2479   2503     if( rc==SQLITE_OK ){
  2480   2504       rc = sqlite3_step(pCur->pFullScan);
  2481   2505       if( rc==SQLITE_ROW ){ pCur->iRow = -1; rc = SQLITE_OK; }
  2482   2506       if( rc==SQLITE_DONE ){ rc = SQLITE_OK; }
  2483   2507     }else{
  2484   2508       pCur->iRow = 0;

Changes to test/spellfix.test.

   216    216     5   kusher     {kosher 16}
   217    217   } {
   218    218     do_execsql_test 5.1.$tn {
   219    219       SELECT word, distance FROM t3 WHERE word MATCH $word
   220    220        ORDER BY score, word LIMIT 1
   221    221     } $res
   222    222   }
          223  +
          224  +#-------------------------------------------------------------------------
          225  +# Try some queries by rowid.
          226  +#
          227  +do_execsql_test 6.1.1 {
          228  +  SELECT word FROM t3 WHERE rowid = 10;
          229  +} {keener}
          230  +do_execsql_test 6.1.2 {
          231  +  SELECT word, distance FROM t3 WHERE rowid = 10;
          232  +} {keener {}}
          233  +do_execsql_test 6.1.3 {
          234  +  SELECT word, distance FROM t3 WHERE rowid = 10 AND word MATCH 'kiiner';
          235  +} {keener 300}
          236  +
          237  +proc trace_callback {sql} {
          238  +  if {[string range $sql 0 2] == "-- "} {
          239  +    lappend ::trace [string range $sql 3 end]
          240  +  }
          241  +}
          242  +
          243  +proc do_tracesql_test {tn sql {res {}}} {
          244  +  set ::trace [list]
          245  +  uplevel [list do_test $tn [subst -nocommands {
          246  +    set vals [execsql {$sql}]
          247  +    concat [set vals] [set ::trace]
          248  +  }] [list {*}$res]]
          249  +}
          250  +
          251  +db trace trace_callback
          252  +do_tracesql_test 6.2.1 {
          253  +  SELECT word FROM t3 WHERE rowid = 10;
          254  +} {keener
          255  +  {SELECT word, rank, NULL, langid, id FROM "main"."t3_vocab" WHERE rowid=?}
          256  +}
          257  +do_tracesql_test 6.2.2 {
          258  +  SELECT word, distance FROM t3 WHERE rowid = 10;
          259  +} {keener {}
          260  +  {SELECT word, rank, NULL, langid, id FROM "main"."t3_vocab" WHERE rowid=?}
          261  +}
          262  +do_tracesql_test 6.2.3 {
          263  +  SELECT word, distance FROM t3 WHERE rowid = 10 AND word MATCH 'kiiner';
          264  +} {keener 300
          265  +  {SELECT id, word, rank, k1  FROM "main"."t3_vocab" WHERE langid=0 AND k2>=?1 AND k2<?2}
          266  +}
          267  +
   223    268   
   224    269   
   225    270   
   226    271   finish_test