Search Backend - PostgreSQL/Sphinx/phpBB Native/MySQL Fulltext -> which one to choose?

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
bug-i
Registered User
Posts: 166
Joined: Fri Feb 17, 2017 12:45 pm

Search Backend - PostgreSQL/Sphinx/phpBB Native/MySQL Fulltext -> which one to choose?

Post by bug-i »

I was wondering which search backend I should pick. Are there any significant differences in function or performance? Using google I coulndt found any good comparison. Which index do you use and why?

PostgreSQL Fulltext
Sphinx Fulltext
phpBB Native Fulltext
MySQL Fulltext
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 6026
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.

Re: Search Backend - PostgreSQL/Sphinx/phpBB Native/MySQL Fulltext -> which one to choose?

Post by thecoalman »

Sphinx is ridiculously fast but you can't simply enable it. It needs to be installed and configured on the server.
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
bug-i
Registered User
Posts: 166
Joined: Fri Feb 17, 2017 12:45 pm

Re: Search Backend - PostgreSQL/Sphinx/phpBB Native/MySQL Fulltext -> which one to choose?

Post by bug-i »

ok, but apart from the speed there are no differences? The search frontend for the user will always be the same? Or are there different operators that he can use?
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 6026
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.

Re: Search Backend - PostgreSQL/Sphinx/phpBB Native/MySQL Fulltext -> which one to choose?

Post by thecoalman »

bug-i wrote: Tue Jan 09, 2018 2:54 pm ok, but apart from the speed there are no differences? The search frontend for the user will always be the same? Or are there different operators that he can use?
There is some minor differences, if I recall correctly mysql backend will allow you to search for phrases in quotes. The phpBB frontend gives you more control over what is indexed without having to mess around with MySQL configuration, e.g. you can edit the script.
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
Tarantino
Registered User
Posts: 875
Joined: Sat Feb 18, 2012 1:51 pm

Re: Search Backend - PostgreSQL/Sphinx/phpBB Native/MySQL Fulltext -> which one to choose?

Post by Tarantino »

One of the big differences for me is:

Sphinx is the fastest but it doesnt work on Shared Hosts.

phpBB Native is maybe the one that will give better search results BUT if you've let say a large board, let say 300Mb of posts table can create a 600mb of search table. (It will depend the configuration on ACP too) But with this one I can search for words with 3, 2 and even 1 letter.

MySQL Fulltext just create an index at database so it go to 2mb maximum compared to the 600mb of phpBB native lol, but the downside is that it can be slower at give the results, but the real bad thing at least for me is that it is limited to words with 4 letters at minimum. I wanted to be able to search too words with 3 letters as "men" and even 2 letters (in my case), its crucial to find information on my board.
This MySQL limitation I guess could be fixed on dedicate server, but on share hosts you cant change the 4 letters limitation :(.

About PostgreSQL this is if I used that kind of sql but I use MySQL instead, so I never new about this neither how it works.
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 6026
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.

Re: Search Backend - PostgreSQL/Sphinx/phpBB Native/MySQL Fulltext -> which one to choose?

Post by thecoalman »

Tarantino wrote: Tue Jan 09, 2018 3:42 pm But with this one I can search for words with 3, 2 and even 1 letter.
There was stopwords function in phpBB3.0, it wasn't enabled but could be. That would allow you prevent the, an etc from being indexed and unique small words would be indexed. I don't know if it still exists in 3.2

but the real bad thing at least for me is that it is limited to words with 4 letters at minimum.
AFAIK this can be changed with MySQL config and it has it's own stopwords function.
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison

Return to “[3.2.x] Support Forum”