MySQL Bugs: #3166: Fulltext Sorting and Counting

Bug #3166	Fulltext Sorting and Counting
Submitted:	14 Mar 2004 9:25	Modified:	29 Dec 2006 11:15
Reporter:	Jim Nguyen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S4 (Feature request)
Version:	4.1	OS:	Windows (Windows, Unix, Linix)
Assigned to:		CPU Architecture:	Any

Description:
I have two feature requests for fulltext search:

1) It would be very beneficial if there was a feature that returned the number of results of a fulltext search efficiently.  Yes, it is possible to COUNT the results of a MATCH but it is VERY SLOW.  I believe it is in O(n) time and has to search through all of the rows.  Returning the count of a fulltext search has many applications and is helpful during programming.

2) It would also be very beneficial if we could sort the results of a MATCH using our own rank column.  We supply the relevancy rating to the MATCH and it would sort the results using our rating instead of MATCH calculating it.  Right now the only way to do that is to to a BOOLEAN MATCH and ORDER BY a column, but this is slow (also O(n) time).  If there are two million rows the time it takes is very noticable.

How to repeat:
Counting results to a MATCH is slow.  Just do a fulltext query and count the results.  It will be very slow if there are a lot of matches.

Suggested fix:
Add new features.