Configure PHPBB search for best quality and performance

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Scam Warning
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
joshkraemer
Registered User
Posts: 9
Joined: Thu Sep 13, 2007 9:09 pm

Configure PHPBB search for best quality and performance

Post by joshkraemer »

I wondering how I can improve the quality and performance of PHPBB considering the size, configuration, and growth of by board. I'm on shared hosting (Bluehost) and don't have any pressing problems right now, but I do get 8-9 slow query dumps each day (mostly from INSERT search/posts). Forum users are registered manually by the admin and search/all forums are restricted to registered users.

Board version: 3.0.5
Current Posts: 17000
New posts each month: 3000
Size of Optimized MySQL DB: 56 MB MyISAM
search_wordmatch table: 34 MB of 56 MB (890,000 records)
search_wordlist table: 2 MB of 56 MB (32,000 records)

My current search configuration:

Enable search facilities: Yes
User search flood interval: 2 seconds
Guest search flood interval: 10 seconds
Search page system load limit: 0
Min author name characters: 3
Maximum number of allowed keywords: 10
Search result cache length: 1800 seconds
Search backend: Fulltext native
Support for non-latin UTF-8 characters using PCRE: Yes (PHP 5.2.9)
Support for non-latin UTF-8 characters using mbstring: Yes
Enable fulltext updating: Yes
Min characters indexed by search: 3
Max characters indexed by search: 20
Common word threshold: 0 %

Any suggestions for improving the search speed and quality of my PHPBB configuration based on the forum stats I've given? What should I change in my configuration? Also, any overall performance tips would be appreciated as well. Thanks for the input!
User avatar
Balint
Registered User
Posts: 952
Joined: Tue Aug 06, 2002 2:19 pm
Location: Germany
Contact:

Re: Configure PHPBB search for best quality and performance

Post by Balint »

Hi,

you could switch to "Fulltext mysql" or - if you want to use "Fulltext native" - increase the Common word threshold to 5% and/or Min characters indexed by search to 4 or 5 maybe?

"Fulltext mysql" works better for me (and for many other users here).


Regards,
Bálint
Sepp71
Registered User
Posts: 78
Joined: Sat Sep 06, 2008 11:32 pm
Location: Germany

Re: Configure PHPBB search for best quality and performance

Post by Sepp71 »

I am interested in this topic, too. My configuration is quite similar to joshkraemer's.
What are the differences between fulltext native and fulltext mySQL?

I have several 3-letter-abbrevations that are used quite often and that are very important so they need to be observed in the results.
Is there anything I can do, do improve results for synonyms like "page 1 paragraph 2 exmpl", also used as "p 1 § 2 ex."?

Thanks a lot!

Sepp
User avatar
Balint
Registered User
Posts: 952
Joined: Tue Aug 06, 2002 2:19 pm
Location: Germany
Contact:

Re: Configure PHPBB search for best quality and performance

Post by Balint »

Hi,

Basically Fulltext Native uses phpBB's own search algorithm by building word-post-pairings. On large forums this information, which is stored in the database, can affect search speed (if my database knowledge is correct). The upside of this method is its independence from the database type (MySQL, MSSQL, Oracle). However, if you have MySQL, you can use its built-in fulltext-search which should be faster. Almost all shared-hosting webhosters use MySQL.

As for the 3-letter-abbreviations: MySQL is configured to index words which are 4 letters or longer. Maybe your webhoster can help you with changing this setting. One-letter-words should be possible with MySQL's fulltext index but I doubt any webhoster will be willing to configure that for you. With phpBB's method it is possible to set the min. characters to "1" but it'll cost you space in the search tables.


Regards,
Bálint
Locked

Return to “[3.0.x] Support Forum”