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

bug-i · Post by **bug-i** » Tue Jan 09, 2018 2:17 pm

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

Post by **thecoalman** » Tue Jan 09, 2018 2:51 pm

Sphinx is ridiculously fast but you can't simply enable it. It needs to be installed and configured on the server.

bug-i · Post by **bug-i** » 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?

Post by **thecoalman** » Tue Jan 09, 2018 3:08 pm

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.

Tarantino · Post by **Tarantino** » Tue Jan 09, 2018 3:42 pm

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.

Post by **thecoalman** » Tue Jan 09, 2018 4:01 pm

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.