SQLite Forum

FTS5: use = or MATCH to compare IDs?
Login
I have an FTS5 table with an ID column pointing to another table -- basically a foreign key:

CREATE TABLE post(id INTEGER PRIMARY KEY, author TEXT);
CREATE VIRTUAL TABLE post_fts USING fts5(id, body);

Given that, should I join them on the id using = or MATCH?

SELECT post.id FROM post INNER JOIN post_fts ON post_fts.id = post.id
SELECT post.id FROM post INNER JOIN post_fts ON post_fts.id MATCH post.id

While using = feels correct, I'm worried that it won't be as performant because FTS5 doesn't allow me to mark post_fts.id as a foreign key. Would it be faster to use MATCH, so it uses the FTS index to find the record?