Can MySQL fulltext search return an index(position) instead of a score?

Question

I would like to use the position/index found by the Match...Against fulltext search in mysql to return some text before and after the match in the field. Is this possible? In all the examples I have seen, the Match...Against returns a score in the select instead of a location or position in the text field of which is being searched.

SELECT 
  random_field, 
  MATCH ($search_fields) 
    AGAINST ('".mysql_real_escape_string(trim($keywords))."' IN BOOLEAN MODE) 
      AS score
FROM indexed_sites 
WHERE 
  MATCH ($search_fields) 
    AGAINST ('".mysql_real_escape_string($keywords)."' IN BOOLEAN MODE) 
ORDER BY score DESC;   

This will give me a field and a score...but I would like an index/position instead of (or along side) a score.

Answer 1

Fulltext searching is a scoring function. its not a search for occurrence function. In other words the highest scoring result may not have a starting position for the match. As it may be a combination of weighted results of different matches within the text. if you include query expansion the search for word/s may not even appear in the result! http://dev.mysql.com/doc/refman/5.0/en/fulltext-query-expansion.html

I hope that makes some sense.

Anyway your best bet is to take the results and then use some text searching function to find the first occurrence of the first matching word. My guess is that would be best suited to a text processing language like perl or a more general language like php or what ever language you are using to run the query.

DC