SQLite Forum

FTS5: use = or MATCH to compare IDs?
Login

FTS5: use = or MATCH to compare IDs?

(1) By anonymous on 2021-09-14 17:11:30 [link]

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?

(2) By sekao_ on 2021-09-17 09:39:44 in reply to 1

Would it be possible to create an index on post_fts.id after creating the table?

(3) By Dan Kennedy (dan) on 2021-09-17 11:18:06 in reply to 2 [link]

Are the values in column post_fts.id a set of distinct integers? If so, you could 
just use the "rowid" field for the id.

Otherwise, assuming they are integer values (with no embedded spaces) in the post_fts.id column, I think MATCH should work quite well. The trouble comes if you have a text value like:

  '123 456 789'

then the constraints "id = ?" and "id MATCH ?" may match different sets of rows. At that point I think you have to use an "external content table" that you can create an index on:

[](https://www.sqlite.org/fts5.html#external_content_and_contentless_tables)


Dan.