0

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

Mise
  • 59
  • 5
  • 1
    "When we do this, we use an INNJER JOIN..." please show that as a MRE as described in the wiki info on the first tag you used. – Yunnosch Dec 07 '20 at 20:16
  • I've edited my original post – Mise Dec 09 '20 at 14:35
  • 1
    Please show a MRE as described in the wiki info on the first tag you used. https://stackoverflow.com/tags/sqlite/info I.e. please read it, make an [mre] which includes a few lines of `creat table ...` and `insert ...`. – Yunnosch Dec 09 '20 at 14:40
  • Very sorry @Yunnosch. I'll fix – Mise Dec 09 '20 at 15:33
  • So, your requirement is: *sort as it would be sorted by time_stamp but without the use of the time_stamp*? – forpas Dec 09 '20 at 15:57
  • @forpas - exactly. Sort by date the row was created ... last created row first It's possible to use the time_stamp column but it really slows the query down when the tables grows – Mise Dec 09 '20 at 16:32
  • Would "Custom Auxiliary Functions" do what I want and use this custom function to change the Rank? – Mise Dec 09 '20 at 23:02

1 Answers1

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?

Mise
  • 59
  • 5