Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Slow COUNT query that uses a fulltext index

hammethammet EntrantInactive User Role Beginner

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)</pre>

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)</pre>

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


  • awadhajawadhaj Entrant Current User Role Beginner
    i think the two-steps query below is much faster;
    at least it will ommit one of your two ft scans.

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


    where id is your pk or an indexed field
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.