« Return to Thread: Fast relevance sorting of full text search results

Fast relevance sorting of full text search results

by Urms :: Rate this Message:

Reply to Author | View in Thread

I'm using pretty standard approach to sorting search results by relevancy:

SELECT DISTINCT product_name,
MATCH (keywords) AGAINST ('CONSOLIDATED* 16* bearing*' IN BOOLEAN MODE) AS rate
FROM _TT
WHERE MATCH ( keywords ) AGAINST ('CONSOLIDATED* 16* bearing*' IN BOOLEAN MODE ) >0
ORDER BY rate DESC

It works fine as long as the quantity of results is not big. Once the quantity is about 50,000 and more (I have a very big database) the query starts working way too slow. Total number of records is about 4 million. It takes about 2 sec when there are 50,000 records in the result but at the same time it takes only about 0.006 sec without ORDER BY clause.

I understand that ORDER BY is time consuming but maybe someone knows a different way to have sorting by relevancy.

Thanks in advance!

 « Return to Thread: Fast relevance sorting of full text search results