MySQL Forums
Forum List  »  Full-Text Search

innodb ft index exact phrase search slow
Posted by: Erik Rooijmans
Date: April 15, 2013 02:13AM

Hi All,

I have a problem regarding exact phrase searching in an innodb full text index.
For the initial test I have added 400.000 text rows (each row consisting of approx. 70 words).

While doing a match against a single word the speed is perfect, however when doing an exact search like match against (' "word1 word2" ') it takes more about 70 seconds

So I started investigating,
- The query is using the index.
- The amount of time taken to run the exact same query for a 2nd time doesn't reduce the overall query time. (So it is not using the cache?) I don't know why this is.

So I started paying with the innodb_ft_cache_size parameter.
By default, if not specified it is set to 8 MB.
By increasing the value to 32 MB and restarting mysql i can indeed see that the cache size has increased.

When running the same query now, it only takes about 39 seconds.

So I dediced to increase the value of innodb_ft_cache_size to 1024 MB.
After restarting mysql and showing its variables, it shows me that the innodb_ft_cache_size is set to 80 MB?

Is this a bug? Or is 80 MB just a limitation?

When I reduce the amount of rows to 100.000 the exact same query runs in and run optimize table the query runs in 2 seconds.

Can somebody help me here?



Options: ReplyQuote

Written By
innodb ft index exact phrase search slow
April 15, 2013 02:13AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.