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

Members

Help

Home

Home » Performance » FullText » Slow COUNT query that uses a fulltext index

Show: Today's Messages :: Show Polls :: Message Navigator

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

Re: Slow COUNT query that uses a fulltext index [message #2301 is a reply to message #2205 ]

Mon, 03 December 2007 03:15

awadhaj
Messages: 1
Registered: December 2007

Junior Member

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

[Updated on: Mon, 03 December 2007 03:16]

Report message to a moderator

Previous Topic:	AND as the default modifier for boolean search
Next Topic:	DB Design for search

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 6 21:34:29 EDT 2009

Total time taken to generate the page: 0.00948 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.