Re: [sqlite] FTS5 performance problem.

[Clemens Ladisch]

John Found wrote:
> Pseudocode: ?1 = keyword + ' ThreadID:' + num2str(threadID);
>
> select count() from PostFTS where PostFTS match ?1;

That works.

> ?1 = keyword
> ?2 = threadID
> select count() from PostFTS where PostFTS match ?1 ThreadID:?2;

That is not valid SQL.  You have to create a single SQL string:

  select count(*) from PostFTS where PostFTS match ?1 || ' ThreadID:' || ?2;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS5 performance problem.

[John Found]

On Sun, 28 May 2017 14:23:47 +0200
Clemens Ladisch  wrote:

> It might be better to use a single FTS lookup for both words:
> 
>   select count() from PostFTS where PostFTS match 'innermost ThreadID:6';
> 

Thanks! This is indeed much faster. But I am passing the search query as a 
parameters. How to proceed then? 

Should I build one composite string query, like this:

Pseudocode: ?1 = keyword + ' ThreadID:' + num2str(threadID);

select count() from PostFTS where PostFTS match ?1;

Or I can make it another way:

?1 = keyword
?2 = threadID
select count() from PostFTS where PostFTS match ?1 ThreadID:?2;

-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS5 performance problem.

[Clemens Ladisch]

John Found wrote:
> the execution time increases by a factor of 100
>
> select count() from PostFTS where PostFTS match 'innermost' and ThreadID = 6;

This is the same as:

  select count() from PostFTS where PostFTS match 'innermost' and PostFTS match 
'ThreadID:6';

It might be better to use a single FTS lookup for both words:

  select count() from PostFTS where PostFTS match 'innermost ThreadID:6';


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users