Slow COUNT query that uses a fulltext index

Polyglot Projects Sphinx & Full-Text Search
1

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 foundFROM 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 foundFROM 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!

2

i think the two-steps query below is much faster;
at least it will ommit one of your two ft scans.

SELECT SQL_CALC_FOUND_ROWS id
FROM t1 WHERE MATCH (fullsearch)
AGAINST (‘+объявлен’ IN BOOLEAN MODE)

SELECT FOUND_ROWS AS found

where id is your pk or an indexed field