/ Check-in [a0ba55ff]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 Unified Diffs Show Whitespace Changes Patch

Changes to ext/misc/spellfix.c.

2047
2048
2049
2050
2051
2052
2053

2054
2055
2056
2057
2058
2059
2060
2061
2062

2063
2064
2065
2066
2067
2068
2069
2070
2071
2072

2073
2074
2075
2076
2077
2078
2079
....
2118
2119
2120
2121
2122
2123
2124









2125
2126
2127
2128
2129
2130
2131
....
2145
2146
2147
2148
2149
2150
2151





2152
2153
2154
2155
2156
2157
2158
....
2461
2462
2463
2464
2465
2466
2467
2468
2469
2470
2471

2472
2473
2474


2475
2476
2477




2478
2479
2480
2481
2482
2483
2484
**
**   (A)    word MATCH $str
**   (B)    langid == $langid
**   (C)    top = $top
**   (D)    scope = $scope
**   (E)    distance < $distance
**   (F)    distance <= $distance

**
** The plan number is a bit mask formed with these bits:
**
**   0x01   (A) is found
**   0x02   (B) is found
**   0x04   (C) is found
**   0x08   (D) is found
**   0x10   (E) is found
**   0x20   (F) is found

**
** filter.argv[*] values contains $str, $langid, $top, and $scope,
** if specified and in that order.
*/
static int spellfix1BestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
  int iPlan = 0;
  int iLangTerm = -1;
  int iTopTerm = -1;
  int iScopeTerm = -1;
  int iDistTerm = -1;

  int i;
  const struct sqlite3_index_constraint *pConstraint;
  pConstraint = pIdxInfo->aConstraint;
  for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){
    if( pConstraint->usable==0 ) continue;

    /* Terms of the form:  word MATCH $str */
................................................................................
     && pConstraint->iColumn==SPELLFIX_COL_DISTANCE
     && (pConstraint->op==SQLITE_INDEX_CONSTRAINT_LT
          || pConstraint->op==SQLITE_INDEX_CONSTRAINT_LE)
    ){
      iPlan |= pConstraint->op==SQLITE_INDEX_CONSTRAINT_LT ? 16 : 32;
      iDistTerm = i;
    }









  }
  if( iPlan&1 ){
    int idx = 2;
    pIdxInfo->idxNum = iPlan;
    if( pIdxInfo->nOrderBy==1
     && pIdxInfo->aOrderBy[0].iColumn==SPELLFIX_COL_SCORE
     && pIdxInfo->aOrderBy[0].desc==0
................................................................................
      pIdxInfo->aConstraintUsage[iScopeTerm].omit = 1;
    }
    if( iPlan&(16|32) ){
      pIdxInfo->aConstraintUsage[iDistTerm].argvIndex = idx++;
      pIdxInfo->aConstraintUsage[iDistTerm].omit = 1;
    }
    pIdxInfo->estimatedCost = 1e5;





  }else{
    pIdxInfo->idxNum = 0;
    pIdxInfo->estimatedCost = 1e50;
  }
  return SQLITE_OK;
}

................................................................................
*/
static int spellfix1FilterForFullScan(
  spellfix1_cursor *pCur,
  int idxNum,
  int argc,
  sqlite3_value **argv
){
  int rc;
  char *zSql;
  spellfix1_vtab *pVTab = pCur->pVTab;
  spellfix1ResetCursor(pCur);

  zSql = sqlite3_mprintf(
     "SELECT word, rank, NULL, langid, id FROM \"%w\".\"%w_vocab\"",
     pVTab->zDbName, pVTab->zTableName);


  if( zSql==0 ) return SQLITE_NOMEM;
  rc = sqlite3_prepare_v2(pVTab->db, zSql, -1, &pCur->pFullScan, 0);
  sqlite3_free(zSql);




  pCur->nRow = pCur->iRow = 0;
  if( rc==SQLITE_OK ){
    rc = sqlite3_step(pCur->pFullScan);
    if( rc==SQLITE_ROW ){ pCur->iRow = -1; rc = SQLITE_OK; }
    if( rc==SQLITE_DONE ){ rc = SQLITE_OK; }
  }else{
    pCur->iRow = 0;







>









>

|








>







 







>
>
>
>
>
>
>
>
>







 







>
>
>
>
>







 







|



>

|
|
>
>



>
>
>
>







2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
....
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
....
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
....
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
2498
2499
2500
2501
2502
2503
2504
2505
2506
2507
2508
**
**   (A)    word MATCH $str
**   (B)    langid == $langid
**   (C)    top = $top
**   (D)    scope = $scope
**   (E)    distance < $distance
**   (F)    distance <= $distance
**   (G)    rowid = $rowid
**
** The plan number is a bit mask formed with these bits:
**
**   0x01   (A) is found
**   0x02   (B) is found
**   0x04   (C) is found
**   0x08   (D) is found
**   0x10   (E) is found
**   0x20   (F) is found
**   0x40   (G) is found
**
** filter.argv[*] values contains $str, $langid, $top, $scope and $rowid
** if specified and in that order.
*/
static int spellfix1BestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
  int iPlan = 0;
  int iLangTerm = -1;
  int iTopTerm = -1;
  int iScopeTerm = -1;
  int iDistTerm = -1;
  int iRowidTerm = -1;
  int i;
  const struct sqlite3_index_constraint *pConstraint;
  pConstraint = pIdxInfo->aConstraint;
  for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){
    if( pConstraint->usable==0 ) continue;

    /* Terms of the form:  word MATCH $str */
................................................................................
     && pConstraint->iColumn==SPELLFIX_COL_DISTANCE
     && (pConstraint->op==SQLITE_INDEX_CONSTRAINT_LT
          || pConstraint->op==SQLITE_INDEX_CONSTRAINT_LE)
    ){
      iPlan |= pConstraint->op==SQLITE_INDEX_CONSTRAINT_LT ? 16 : 32;
      iDistTerm = i;
    }

    /* Terms of the form:  distance < $dist or distance <= $dist */
    if( (iPlan & 64)==0
     && pConstraint->iColumn<0
     && pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ
    ){
      iPlan |= 64;
      iRowidTerm = i;
    }
  }
  if( iPlan&1 ){
    int idx = 2;
    pIdxInfo->idxNum = iPlan;
    if( pIdxInfo->nOrderBy==1
     && pIdxInfo->aOrderBy[0].iColumn==SPELLFIX_COL_SCORE
     && pIdxInfo->aOrderBy[0].desc==0
................................................................................
      pIdxInfo->aConstraintUsage[iScopeTerm].omit = 1;
    }
    if( iPlan&(16|32) ){
      pIdxInfo->aConstraintUsage[iDistTerm].argvIndex = idx++;
      pIdxInfo->aConstraintUsage[iDistTerm].omit = 1;
    }
    pIdxInfo->estimatedCost = 1e5;
  }else if( (iPlan & 64) ){
    pIdxInfo->idxNum = 64;
    pIdxInfo->aConstraintUsage[iRowidTerm].argvIndex = 1;
    pIdxInfo->aConstraintUsage[iRowidTerm].omit = 1;
    pIdxInfo->estimatedCost = 5;
  }else{
    pIdxInfo->idxNum = 0;
    pIdxInfo->estimatedCost = 1e50;
  }
  return SQLITE_OK;
}

................................................................................
*/
static int spellfix1FilterForFullScan(
  spellfix1_cursor *pCur,
  int idxNum,
  int argc,
  sqlite3_value **argv
){
  int rc = SQLITE_OK;
  char *zSql;
  spellfix1_vtab *pVTab = pCur->pVTab;
  spellfix1ResetCursor(pCur);
  assert( idxNum==0 || idxNum==64 );
  zSql = sqlite3_mprintf(
     "SELECT word, rank, NULL, langid, id FROM \"%w\".\"%w_vocab\"%s",
     pVTab->zDbName, pVTab->zTableName,
     ((idxNum & 64) ? " WHERE rowid=?" : "")
  );
  if( zSql==0 ) return SQLITE_NOMEM;
  rc = sqlite3_prepare_v2(pVTab->db, zSql, -1, &pCur->pFullScan, 0);
  sqlite3_free(zSql);
  if( rc==SQLITE_OK && (idxNum & 64) ){
    assert( argc==1 );
    rc = sqlite3_bind_value(pCur->pFullScan, 1, argv[0]);
  }
  pCur->nRow = pCur->iRow = 0;
  if( rc==SQLITE_OK ){
    rc = sqlite3_step(pCur->pFullScan);
    if( rc==SQLITE_ROW ){ pCur->iRow = -1; rc = SQLITE_OK; }
    if( rc==SQLITE_DONE ){ rc = SQLITE_OK; }
  }else{
    pCur->iRow = 0;

Changes to test/spellfix.test.

216
217
218
219
220
221
222
223


224









225


































226
  5   kusher     {kosher 16}
} {
  do_execsql_test 5.1.$tn {
    SELECT word, distance FROM t3 WHERE word MATCH $word
     ORDER BY score, word LIMIT 1
  } $res
}
















































finish_test








>
>
|
>
>
>
>
>
>
>
>
>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
  5   kusher     {kosher 16}
} {
  do_execsql_test 5.1.$tn {
    SELECT word, distance FROM t3 WHERE word MATCH $word
     ORDER BY score, word LIMIT 1
  } $res
}

#-------------------------------------------------------------------------
# Try some queries by rowid.
#
do_execsql_test 6.1.1 {
  SELECT word FROM t3 WHERE rowid = 10;
} {keener}
do_execsql_test 6.1.2 {
  SELECT word, distance FROM t3 WHERE rowid = 10;
} {keener {}}
do_execsql_test 6.1.3 {
  SELECT word, distance FROM t3 WHERE rowid = 10 AND word MATCH 'kiiner';
} {keener 300}

proc trace_callback {sql} {
  if {[string range $sql 0 2] == "-- "} {
    lappend ::trace [string range $sql 3 end]
  }
}

proc do_tracesql_test {tn sql {res {}}} {
  set ::trace [list]
  uplevel [list do_test $tn [subst -nocommands {
    set vals [execsql {$sql}]
    concat [set vals] [set ::trace]
  }] [list {*}$res]]
}

db trace trace_callback
do_tracesql_test 6.2.1 {
  SELECT word FROM t3 WHERE rowid = 10;
} {keener
  {SELECT word, rank, NULL, langid, id FROM "main"."t3_vocab" WHERE rowid=?}
}
do_tracesql_test 6.2.2 {
  SELECT word, distance FROM t3 WHERE rowid = 10;
} {keener {}
  {SELECT word, rank, NULL, langid, id FROM "main"."t3_vocab" WHERE rowid=?}
}
do_tracesql_test 6.2.3 {
  SELECT word, distance FROM t3 WHERE rowid = 10 AND word MATCH 'kiiner';
} {keener 300
  {SELECT id, word, rank, k1  FROM "main"."t3_vocab" WHERE langid=0 AND k2>=?1 AND k2<?2}
}




finish_test