I too am interested in this answer, I still have to start using fts5. What would be interesting is to see the `EXPLAIN QUERY PLAN [query]` for each of your queries, so as to see what causes the slowness.
On Thu, Feb 8, 2018, 7:14 PM John Found, <johnfo...@asm32.info> wrote: > > I am using FTS5 for pretty complex search in my application, but recently, > trying to make it even more complex I faced some problems that are more > general than only FTS5. > > I have a forum engine where are several tables for the threads, for the > posts, for the users etc. At first I want to be able to search in the posts > text, but moreover, this search have to be limiter to some subset of the > posts, for example in the posts of a particular thread or posts of some > user. Also, there are cases where free-text search is not actually > necessary, for example when I am searching for all posts from a particular > user. > > At first, I tried to create a FTS5 table, containing only the text data > that need to be searched and then to access it by queries of the type: > > select > some, > fields > from > fts > left join posts p on p.id = fts.rowid > left join threads t on t.id = p.threadid > left join users u on u.id = p.userid > where > fts match ?1 and u.nick = ?2 and t.id = ?3 > order by ORDER > > Such queries are pretty fast when there is only fts match directive in the > where clause. > But any additional condition added ruins the performance, especially if > the fts match returns big amount of matches. > > Additional problem is the order by clause. If the ORDER BY term is "rank" > everything works great, but changing it to > other field (for example the post time in order to get first most recent > posts) causes huge slow down of the query. > > My second attempt was to sacrifice space for speed and to put all > searchable data in the fts table - post text, the thread titles and the > usernames. This way, building complex fts queries kind of: > > (content: ?1 OR caption: ?2) AND thread: ?3 AND user: ?4 > > I can leave only the fts query in the WHERE clause. This way, the search > is pretty fast, but the huge problem remains > the ORDER BY clause. Again everything works fine with "rank", but attempts > to use any other field for sorting, causes > huge probems: slow downs up to tens of seconds (usual search time is few > milliseconds) and out of memory errors. > > Such problems with this second approach are even more serious than on the > first approach. i.e. with the second approach everything works fine and > quick with "rank" order by, and very, very slow and with errors, on any > other "order by" option. > > So, he main question follows: > > What is the right way to design such complex search systems, based on FTS? > How to properly approach the sorting of the search results in order to not > have so big slowdowns and out of memory errors. > > Any tips are highly welcome! > > Regards > -- > http://fresh.flatassembler.net > http://asm32.info > John Found <johnfo...@asm32.info> > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
