Full-text ordering

Sharif

Hey I am wondering in a fulll-text search, if you don't put the ORDER BY clause, how does it order the results?

Roger_Ramjet

RTFM it tells you there.

Sharif

I have read it, just did not understand it. It said by the order of relavance? How does that work.

Roger_Ramjet

It calculates a number something along these lines

Number the words sequentially, starting at the back. S

Add up the S numbers of your matched words. M

= how many times are your words matched and how close to the begining are they. This gives the importance of your matched words within the document/search field.

Count how many words there are. T

Count how many unique words there are. U

Divide T by U. O

= the higher this number is, the more important any individual word is within the whole document.

Multiply the number and importance of your matched words M by the relevence of any match O = overall relevence of this doc.

Of course, most of this calculation is done when the full text index is built. When you run the query, the stored values are just summed for each of your search words.

THIS IS NOT THE ALGORITHM ACTUALLY USED. Before some one jumps on me. It is just a simplistic explantion of the principles used. The actual math involved is these days way beyond me.

Roger_Ramjet

And of course it is sort descending in my example, the highest number is the first row.

Sharif

So it is better not to specify a order by clause then?

Roger_Ramjet

Up to you. Not knowing what your search is for, all I can say is 'suck it and see'. Run some tets and see which one you think produces the best result. And then get some users to do the same. Or you could get whack and let the user choose the sort order??