I am trying to use FULLTEXT search as a preliminary filter before fetching data from another table. Consecutive JOINs follow to further refine the query and to mix-and-match rows (in reality there are up to 6 JOINs of the main table).

The first "filter" returns the IDs of the rows that are useful, so after joining I have a subset to continue with. My issue is performance, however, and my lack of understanding of how the SQL query is executed in SQLite.

SELECT *
FROM mytbl AS t1
JOIN
(SELECT someid
    FROM myftstbl
    WHERE
    myftstbl MATCH 'MATCHME') AS prior
ON
    t1.someid = prior.someid
    AND t1.othercol = 'somevalue'

JOIN mytbl AS t2
ON
    t2.someid = prior.someid
    /* Or is this faster? t2.someid = t1.someid */

My thought process for the query above is that first, we retrieve the matched IDs from the myftstbl table and use those to JOIN on the main table t1 to get a sub-selection. Then we again JOIN a duplicate of the main table as t2. The part that I am unsure of is which approach would be faster: using the IDs from the matches, or from t2?

In other words: when I refer to t1.someid inside the second JOIN, does that contain only the someids after the first JOIN (so only those at the intersection of prior and those for which t1.othercol = 'somevalue) OR does it contain all the original someids of the whole original table?

You can assume that all columns are indexed. In fact, when I use one or the other approach, I find with EXPLAIN QUERY PLAN that different indices are being used for each query. So there must be a difference between the two.

CodePudding user response：

The query should be simplified to

SELECT *
FROM mytbl AS t1
JOIN myftstbl USING (someid)         -- or ON t1.someid = myftstbl.someid
JOIN mytbl AS t2 USING (someid)      -- or ON t1.someid = t2.someid
WHERE myftstbl.{???} MATCH 'MATCHME' -- replace {???} with correct column name
  AND t1.othercol = 'somevalue'

PS. The query logic is not clear for me, so it is saved as-is.