Re: Full-text select count(primary key) always slow with only 7000 rows

[Faury Rodriguez]

Counting your results when using a fulltext index will be slow and get slower as your table size increases; however, this may be helpful since your table is still relatively small.

I assume you execute a similar query that uses the full text index to get information like the actual id and then run the select count query just to get the # of results. If that's the case, run that main fulltext query first and add "SQL_CALC_FOUND_ROWS" after the "SELECT". Subsequently, execute "SELECT FOUND_ROWS()" instead of the select count. See: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

Again, this will also get slower as your data increases.

If you can integrate it, check out Sphinx Search, www.sphinxsearch.com.
This provides a more scalable solution to full text searching and gives you a count of search hits for "free" as part of your search request.