List:General Discussion« Previous MessageNext Message »
From:Sebastian Mendel Date:January 9 2008 9:24am
Subject:Re: Fast relevance sorting of full text search results
View as plain text  
Urms schrieb:
> 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. 

you should reformat your query or table structure

for a quick solution:

probably with 50.000 records it exceeds your myisam_sort_buffer_size or
sort_buffer_size, try to raise them

-- 
Sebastian

Thread
Fast relevance sorting of full text search resultsUrms2 Jan
  • Re: Fast relevance sorting of full text search resultsShawn Green6 Jan
  • Re: Fast relevance sorting of full text search resultsSebastian Mendel9 Jan