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: |
a0ba55ff0596c5f15e9cdb254c68ef50 |
| 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
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
|