Slow count(*) query

Hi,

I have a slow count query that uses a fulltext index which I need to
optimize but I can't figure out why it goes so slow. The query as it
stands takes about ~30 sec to execute.

Here is the query and the EXPLAIN.

SELECT count(*) FROM sites
WHERE MATCH(title, url, stripped) AGAINST('test' IN BOOLEAN MODE) AND
stat = 'A'

id select_type TABLE type possible_keys KEY key_len
ref rows Extra
1 SIMPLE sites fulltext stat,match_url_title_stripped
match_url_title_stripped 0 1 USING WHERE

I've been trying to fix this for quite some time but can't figure out
what is wrong. Any insight would be appreciated!

Found this post on mysql.com
should be helpful
http://dev.mysql.com/doc/refman/5.0/...xt-search.html

After tons of hours today working on this, I HAVE FINALLY MASTERED THE
TECHNIQUE OF USING THIS THING!!!!

# This query is what you send to your MySQL to return the results. I put
the LIMIT so that you don't overflow.

$query_ret="SELECT ProductID, Title, Description, Price, RetailPrice,
MATCH (Title) AGAINST ('$keyword') AS score FROM server.book HAVING
score > 0 LIMIT $start, $maxret;";

# This query will COUNT the number of rows it found. (I believe that's
correct), I don't believe it counts occurrences, just rows. I saw that
if you pull back without a LIMIT above, and count that way, it's 100000x
slower. So do your count like this:

$query_count="SELECT count(MATCH(Title) AGAINST('$keyword')) AS score
FROM server.book WHERE MATCH (Title) AGAINST ('$keyword') HAVING score >
0;";

Make sure you have your Primary Key setup, your Title and Description as
SEPARATE FULLTEXT INDEXES. I spent a few hours boggling over this.

Nathan

PKalos@gmail.com wrote:
> Hi,
>
> I have a slow count query that uses a fulltext index which I need to
> optimize but I can't figure out why it goes so slow. The query as it
> stands takes about ~30 sec to execute.
>
> Here is the query and the EXPLAIN.
>
> SELECT count(*) FROM sites
> WHERE MATCH(title, url, stripped) AGAINST('test' IN BOOLEAN MODE) AND
> stat = 'A'
>
> id select_type TABLE type possible_keys KEY key_len
> ref rows Extra
> 1 SIMPLE sites fulltext stat,match_url_title_stripped
> match_url_title_stripped 0 1 USING WHERE
>
> I've been trying to fix this for quite some time but can't figure out
> what is wrong. Any insight would be appreciated!
>