Continue the legacy...

Merri · Posted: Tue Nov 03, 2009 10:22 pm Post subject: mysql fulltext search

phpBB.com original thread

I have applied this change to a forum with 180 000+ messages. Unfortunatenaly I'm unable to see any change in speed after building a fulltext index for POSTS_TEXT_TABLE and I am quite disappointed. It takes ten seconds minimum for a search query, and it took this long even before having the fulltext.

Wonder if anyone may have any clue what might have gone wrong. At the moment I'm trying to see if it has any difference that I had the fulltext fields separated to post_subject and post_text and not as just one index with both of them.

Dog Cow · Board Member Joined: 18 Nov 2008 Posts: 378

Does the MySQL server have enough RAM? From what I've read, to get the best performance, the total index should fit into RAM.

Merri · Posted: Wed Nov 04, 2009 7:00 pm Post subject: Re: mysql fulltext search

That would then be a problem, the site is on a regular shared server on a host. Although I do get it for free so I can't complain about that (sponsored site). The size of the index is nearly 400 MB so it is rather impossible to have it in memory entirely (when you do account the server is hosting I-don't-know-how-many other sites).

dondino · Board Member Joined: 09 Dec 2008 Posts: 144

Oh my godness! I would love to see this phpbb2 forum with more or less.. 180.000 messages!!

well, 10 seconds I think are quite fair for such big database...