Re: speeding up an FTS5 search with a JOIN

On 7/3/63 14:58, P Kishor wrote:
> I asked this question on Stackoverflow with not much success, and a suggestion to ask it on the list. So here I am. I have two tables, t1(id, t1Id, … other cols …, fullText) and a FTS5 virtual table vt1(t1Id, fullText)
>
> ```
> sqlite> EXPLAIN QUERY PLAN
>     ...> SELECT Count(*) as num FROM t1 WHERE deleted = 0;
>
> QUERY
> PLAN
> --SEARCH TABLE t1 USING COVERING INDEX ix_t1_t1Id (deleted=?)
>
> sqlite> SELECT Count(*) as num FROM t1 WHERE deleted = 0;
> 308498
>
> Run Time
> : real 0.043 user 0.023668 sys 0.009005
> ```
>
>
> As can be see above, the actual query takes ~43ms
>
> ```
> sqlite> EXPLAIN QUERY PLAN
>     ...> SELECT Count(*) as num FROM vt1 WHERE vt1 MATCH 'foo';
>
> QUERY
> PLAN
> --SCAN TABLE vt1 VIRTUAL TABLE INDEX 131073:
>
> sqlite> SELECT Count(*) as num FROM vt1 WHERE vt1 MATCH 'foo';
> 80789
>
> Run Time
> : real 0.047 user 0.008021 sys 0.009640
> ```
>
> 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


That does seem slow. Are there many rows in table "t1" with t1.deleted 
set to something other than 0? What does:

   SELECT count(*) AS num FROM t1 JOIN vt1 ON t1.t1Id=vt1.t1Id WHERE vt1 
MATCH 'foo';

return?

Dan.




>
> Run Time
> : real 26.218 user 1.396376 sys 5.413630
> ```
>
> The answer is correct but the query takes more than 26 seconds! Of course, I would like to speed up this query by several orders of magnitude, but I would also like to understand why this join is causing the slowdown.
>
> Now, the reason I have constructed a query like this is because users can add further constraints for the table t1. For example,
>
> ```
> sqlite> SELECT Count(*) as num
>     ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
>     ...> WHERE t1.deleted = 0 AND
>     ...> WHERE t1.frob = ‘bar' AND
>     ...> WHERE t1.nob = ‘baz' AND
>     ...> vt1 MATCH 'foo’;
> ```
>
> Also, in every operation, for every given constraint, two queries are performed, one that returns the count and the other that returns the actual columns. And, finally, only a subset of the results are returned using LIMIT and OFFSET but *after* a sort ORDER has been prescribed. So, in reality, the last constraint above would result in the following
>
> ```
> sqlite> SELECT Count(*) as num
>     ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
>     ...> WHERE t1.deleted = 0 AND
>     ...> WHERE t1.frob = ‘bar' AND
>     ...> WHERE t1.nob = ‘baz' AND
>     ...> vt1 MATCH 'foo’;
> 20367
>
> sqlite> SELECT t1.id, t1.t1Id, … other cols …,
>     ...> snippet(vt1, 1, "<b>", "</b>", "", 50) AS context,
>     ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
>     ...> WHERE t1.deleted = 0 AND
>     ...> WHERE t1.frob = ‘bar' AND
>     ...> WHERE t1.nob = ‘baz' AND
>     ...> vt1 MATCH ‘foo’
>     ...> ORDER BY <some t1 col>
>     ...> LIMIT 30 OFFSET <some offset calculated by pagination>;
> ```
>
> When no t1 columns are prescribed in the constraint, the default count (shown above) and default cols are returned with the FTS search
>
> ```
> sqlite> SELECT Count(*) as num
>     ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
>     ...> WHERE t1.deleted = 0
>     ...> vt1 MATCH 'foo’;
> 20367
>
> sqlite> SELECT t1.id, t1.t1Id, … other cols …,
>     ...> snippet(vt1, 1, "<b>", "</b>", "", 50) AS context,
>     ...> FROM t1 JOIN vt1 ON t1.t1Id = vt1.t1Id
>     ...> WHERE t1.deleted = 0
>     ...> vt1 MATCH ‘foo’
>     ...> ORDER BY <some t1 col>
>     ...> LIMIT 30 OFFSET 0;
> ```
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users