On 03/17/2016 12:27 PM, Christoforus Surjoputro wrote: > Hi all. I've example database that can be downloaded here > http://www.megafileupload.com/rhqU/product.db or you can generate yourself by > using this python script:http://pastebin.com/4djPZJ21 > > I try to compare performance between standard query and using matchinfo() > function.Standard queryt:http://pastebin.com/RFZwx8AJ > > matchinfo():http://pastebin.com/BBq28HNh > > Script that use matchinfo() function about 20x slower than standard query. Is > that normal or something wrong with my query or my database? And how to > improve the matchinfo() performance?
Because your query has a LIMIT 1 clause, FTS3 usually has to find just the first result that matches the full-text query. However, a matchinfo call with the 'x' flag specified has to loop through all the rows that match the query, as one of the values returned by 'x' is the total number of matches in all rows. In FTS3 there is no way to get this information without iterating through all matching rows. FTS5 is more faster and more flexible, IMO. Dan.
