Have a question about full text search.
I have a query that works, but I want to improve it. For example, suppose I'm searching for "best solution". In my result (using FTS,CONTAINSTABLE,NEAR,ISABOUT,*) I have the columns rank, id, string:
rank| id | string ----+----+------- 430 | 33 | my own best solution 430 | 32 | the way for best solution 430 | 30 | the best solution sample 430 | 31 | best solution 430 | 34 | best solution creation how 300 | 40 | usefull best software solution 300 | 41 | best software solution 200 | 50 | wolrds bests solutions 200 | 51 | bests solutions of the world
So this query is 100% right for me, and all RANKs are correct, but I want to make this query more relevant.
If a keyword's position in a string is more to the left, it should appear earlier in the result. For example:
rank| id | string ----+----+------- 430 | 31 | best solution 430 | 34 | best solution creation how 430 | 30 | the best solution sample 430 | 33 | my own best solution 430 | 32 | the way for best solution 300 | 41 | best software solution 300 | 40 | usefull best software solution 200 | 51 | bests solutions of the world 200 | 50 | wolrds bests solutions
Is this possible? If so, how can I get this result?
You need an indexOf function. Search your sql manual for a similar function.
Then add a sorting criteria looking like ORDER BY rank, CASE indexOf(string, 'best') WHEN -1 THEN 100000 ELSE indexOf(string, 'best') END.
If you can't find a function doing a kind of indexOf, write it yourself using the CREATE FUNCTION feature of your database manager.