SQLite:fts5 and rank, sort by date

Question

We're using SQLite and FTS5 in our appliction. It's working well and is extremely fast. However, we have hit a problem when our virtual fts table grows large. We need to sort our search results by date in reverse chronological order and not by the default rank and also limit to 100. In English, we need the 100 most recent rows that match a search string.

CREATE VIRTUAL TABLE ft USING fts5(time_stamp, data);
INSERT INTO ft(time_stamp, data) VALUES('1', 'foo 1');
INSERT INTO ft(time_stamp, data) VALUES('2', 'foo 2');
INSERT INTO ft(time_stamp, data) VALUES('3', 'foo 3');
SELECT * FROM ft WHERE data MATCH ('foo*') ORDER BY Rank LIMIT 2;

The results from the above query look like:

-----------------
|time_stamp|data
-----------------
|1         |foo 1
-----------------
|2         |foo 2
-----------------

And you can see, the rank is the same for all enteries and the default sort (after Rank) is by date. Is there a way to use fts5 to sort by reverse date and completely ignore Rank?

What I have below works ... but has terrible performace implications once the table grows large.

CREATE VIRTUAL TABLE ft USING fts5(time_stamp, data);
INSERT INTO ft(time_stamp, data) VALUES('1', 'foo 1');
INSERT INTO ft(time_stamp, data) VALUES('2', 'foo 2');
INSERT INTO ft(time_stamp, data) VALUES('3', 'foo 3');
SELECT * FROM ft WHERE data MATCH ('foo*') ORDER BY time_stamp DESC LIMIT 2;

The results from the above query look like:

-----------------
|time_stamp|data
-----------------
|3         |foo 3
-----------------
|2         |foo 2
-----------------

This is exactly what we're looking for ... but we need a way to achieve it without using the sort on time_stamp

Answer 1

If I use ROWID, I get the desired result.

SELECT ROWID, time_stamp, data FROM ft WHERE data MATCH ('foo*') ORDER BY ROWID DESC LIMIT 2;

-----------------------
|rowid|time_stamp|data
-----------------------
|3    |3         |foo 3
-----------------------
|2    |2         |foo 2
-----------------------

Is it OK to use ROWID?