MySQL Forums
Forum List  »  Full-Text Search

Re: Full-text select count(primary key) always slow with only 7000 rows
Posted by: Faury Rodriguez
Date: June 30, 2008 12:06PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Full-text select count(primary key) always slow with only 7000 rows
4232
June 30, 2008 12:06PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.