Hi Dan,
> On Sat Mar 7 13:32:54 UTC 2020,Dan Kennedy danielk1977 at gmail.com wrote: >> On 7/3/63 14:58, P Kishor wrote: [snipped] >> The actual query, in this case, takes ~47ms. So far so good. But the problem >> occurs when I join the two tables >> >> ``` >> sqlite> EXPLAIN QUERY PLAN >> ...> SELECT Count(*) as num >> ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id >> ...> WHERE t1.deleted = 0 AND vt1 MATCH 'foo'; >> >> QUERY >> PLAN >> |--SCAN TABLE vt1 VIRTUAL TABLE INDEX 0:m >> --SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (t1Id=?) >> >> sqlite> SELECT Count(*) as num >> ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id >> ...> WHERE t1.deleted = 0 AND vt1 MATCH 'foo'; >> 80789 >> >> Run Time >> : real 26.218 user 1.396376 sys 5.413630 >> ``` >That does seem slow. Are there many rows in table "t1" with t1.deleted set to >something other than 0? No, none of them have `deleted != 0`. Explanation: `deleted` is a flag field that will track (in the future) when any of the rows are tagged as deleted, no longer in use. The idea is to exclude “deleted” rows from searches, but not really delete them. As of now, all the rows are active, so the column is set to 0 (false) for all the rows. In other words, ``` sqlite> SELECT count(*) FROM t1 WHERE deleted = 0; 308498 sqlite> SELECT count(*) FROM t1 WHERE deleted != 0; 0 ``` > What does: SELECT count(*) AS num FROM t1 JOIN vt1 ON t1.t1Id=vt1.t1Id > WHERE vt1 MATCH 'foo'; return? Dan. sqlite> SELECT count(*) AS num FROM t1 JOIN vt1 ON t1.t1Id=vt1.t1Id WHERE vt1 MATCH 'foo'; 80789 [snipped] Please also see my follow-up email with an update sent yesterday, Mar 8 at 18:44:14 UTC 2020 Many thanks. Note: I had accidentally set the wrong mail-delivery flag on my subscription so I didn’t get earlier emails. It has been set right now. There is a possibility this email may not be a part of the original thread, in which case, my apologies in advance. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
