MySQL Performance: FullText => Slow COUNT query that uses a fulltext index

Slow COUNT query that uses a fulltext index [message #2205]

Thu, 15 November 2007 10:12

hammet
Messages: 1
Registered: November 2007
Location: Ukraine / Donetsk

Junior Member

Hi,

I have a slow count(*) query that uses a fulltext index and i can't find out why execution time is so awful. The query as it
stands takes about ~2-5 sec to execute, depends of found rows quantity.

Here's the query:

SELECT COUNT(*) AS found
FROM `t1` WHERE MATCH (fullsearch) 
AGAINST ('+объявлен' IN BOOLEAN MODE)

later i've found that this query goes faster:

SELECT COUNT(MATCH (fullsearch) 
AGAINST ('+объявлен' IN BOOLEAN MODE)) AS found
FROM `t1` WHERE MATCH (fullsearch) 
AGAINST ('+объявлен' IN BOOLEAN MODE)

the results are follow:
Found 161354 of 322271 rows.
Execution time: 3.4158 sec.

Here is EXPLAIN:
id: 1
select_type: SIMPLE
table: t1
type: fulltext
possible_keys: fullsearch
key: fullsearch
key_len: 0
ref:
rows: 1
Extra: Using where

What can i do to count found rows? is there any suggestion that can be applied to speed this up?

Any insight would be appreciated!

Report message to a moderator