0

I have a table that is Full Text indexed, on which I query using FREETEXT. The table is filled from other tables that have constant data, rarely changed. Now there are few millions of rows.

Table structure:

ID (identity), PK
Text (varchar), FT IX
ForeignID (int) -- of the original ID in it's original table

I'm not satisfied with the results, because sometimes a word is repeated too many times and therefore the row gets high ranked by the engine. Example: "Gas Station Young, Young street, Some State" is higher than "Young street" for searching "young". Ranking a table is enough (no need to rank rows inside the table besides what the FTS index already does).

I want to add a simple custom ranking mechnism.

I've tried adding a CustomRank column to order by while/ before querying with FREETEXT but it takes too long. The execution plan says that sorting by CustomRank is the weakest link in the chain.

I thought about:

  1. Managing another field, so I would have one for display (DisplayText) and one for query/ index (IndexText), and manipulating the query/ index, by repeating words and phrases for rows that I want to promote.
  2. Splitting it to tables and deal with it in the code. Each table will get it's own rank.

Any ideas? Thanks, Wanger.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hen Wagner
  • 201
  • 3
  • 12
  • Have you looked at [`FREETEXTTABLE()`](http://msdn.microsoft.com/en-us/library/ms177652.aspx)? It returns a ranking, although I don't know if it meets your needs; to get a better answer you'll need to explain exactly how you want to rank the text. Showing some sample table data would be helpful. – Pondlife Apr 03 '13 at 14:11
  • The rank returned there is the FTS rank. In the past I reflected it to a rating control but to be honest it is useless. exmple: – Hen Wagner Apr 03 '13 at 18:51
  • +----+---------------------------------------------+------------+ | ID | Text | ForeignID | +----+---------------------------------------------+------------+ | 1 | Gas Station Young, Young street, Some State | 1054232 | | 2 | Young street, Some State | 1002892 | | 3 | Highway A5 | 1002892 | +----+---------------------------------------------+------------+ – Hen Wagner Apr 03 '13 at 18:58
  • 1
    Please edit your question to add sample data or code: it's unreadable in the comments. And if the FTS ranking isn't useful, it would help to explain what your own ranking would be and how you calculate it. You might also want to look at [Lucene](http://lucene.apache.org/core/), it's a fairly common alternative to FTS and may be [closer to what you need](http://lucene.apache.org/core/3_6_2/scoring.html). – Pondlife Apr 03 '13 at 19:26

0 Answers0