Search Backend Selection

Discussion of general topics related to the new version and its place in the world. Don't discuss new features, report bugs, ask for support, et cetera. Don't use this to spam for other boards or attack those boards!
Forum rules
Discussion of general topics related to the new release and its place in the world. Don't discuss new features, report bugs, ask for support, et cetera. Don't use this to spam for other boards or attack those boards!
User avatar
stubbers
Registered User
Posts: 406
Joined: Sat Oct 23, 2004 10:36 pm
Location: LoSt
Contact:

Search Backend Selection

Post by stubbers » Mon Jul 10, 2006 4:20 am

Gday all,
Just wondering, for those of you that are very competent in MySQL and understand the backend of phpBB, what's the difference between using MySQL as your search backend and phpBB... Apart from using different applications. What's faster, advantages, disadvantages...

Cheers,
Stubbers

User avatar
stubbers
Registered User
Posts: 406
Joined: Sat Oct 23, 2004 10:36 pm
Location: LoSt
Contact:

Re: Search Backend Selection

Post by stubbers » Tue Jul 11, 2006 1:55 am

Come on, there has to be someone out there that knows the difference...

NeoThermic
Registered User
Posts: 198
Joined: Fri Jan 02, 2004 3:44 pm
Location: United Kingdom
Contact:

Re: Search Backend Selection

Post by NeoThermic » Tue Jul 11, 2006 2:35 am

The main one is the way the search is comprised. Doing a search tables route means that two tables must be cross-referenced with words and the posts that contain them, while doing pruning for common words. This creates a lot of rows (for example, my 153,119 posts phpBB3 forum has 2,542,750 rows in the word-match table, and 100,209 in the word list table). All in all, this added roughly 44MB to the database).

Doing a MySQL fulltext index doesn't require any extra tables, and only added 17MB to the same set of data. In theroy it should also make searches quicker, as one doesn't have to involve two extra tables to do a search. There is, however, some amazing maths behind the MySQL fulltext engine; if you wish to have a look at them, I would suggest dissecting mysql fulltext indexing.

NeoThermic
phpBB release date pool!
The NeoThermic.com... a well of information. Ask me for the bit bucket so you can drink its goodness. ||新熱です

Graham
Registered User
Posts: 1304
Joined: Tue Mar 19, 2002 7:11 pm
Location: UK

Re: Search Backend Selection

Post by Graham » Tue Jul 11, 2006 5:21 pm

The simple answer is that it all depends on your board. The main advantage of the phpBB (native) backend is that it will work on any database. The downside is that it will not scale to very large boards. The advantage of using a DBMS specific one is that it will (generally) be faster on larger boards and scale further, although clearly a specific one for your DBMS must exist
"So Long, and Thanks for All the Fish"

Graham
Eeek, a blog!

code reader
Registered User
Posts: 653
Joined: Wed Sep 21, 2005 3:01 pm

Re: Search Backend Selection

Post by code reader » Tue Jul 11, 2006 6:57 pm

in addition to being smaller and faster, native mysql fulltext has these added advantages:
  • it allows you to search for phrase by using quotes
  • it has boolean search, such as +word1 + word2 -word3 (posts that contain word 1 and 2 but not 3)
  • it is better with wildcards, allowing both single-letter (?) and any-number-of-letters (*) wildcards.
( :oops: in contrast with my name, i did not actually read the code or tested phpbb implementation of mysql fulltext search, so i dont know if these extra features are accessible through phpbb interface. if not, it should be a simple mod to enable them)

mysql fulltext search has one disadvantage: if you use it, you cant decide which forums will be indexed and which wont: you have to index all forums.

the speed and size advantage are very substantial. in fact, the phpbb built-in search is bad enough that if not disabled, in reasonably sized boards, posting and post edit become so slow as to present issues, both with users' satisfaction and with double-posting. all the large phpbb boards i know of either tweak or disable phpbb search. (including phpbb.com, which use "vanilla" phpbb code religously in any functionality except search).

so, imo, for anyone whose backend is mysql, it is a no-brainer: use mysql fulltext search.

NeoThermic
Registered User
Posts: 198
Joined: Fri Jan 02, 2004 3:44 pm
Location: United Kingdom
Contact:

Re: Search Backend Selection

Post by NeoThermic » Tue Jul 11, 2006 7:04 pm

code reader wrote: including phpbb.com, which use "vanilla" phpbb code religously in any functionality except search.


Actually, that is the MySQL config terminating large results, not any added code. The search.php on phpBB.com is the same as the one you can obtain from the downloads page.

NeoThermic
phpBB release date pool!
The NeoThermic.com... a well of information. Ask me for the bit bucket so you can drink its goodness. ||新熱です

code reader
Registered User
Posts: 653
Joined: Wed Sep 21, 2005 3:01 pm

Re: Search Backend Selection

Post by code reader » Tue Jul 11, 2006 7:15 pm

my bad.
the correct phrasing should be then:
"phpbb built-in search is so bad, that in phpbb.com, which is the only large board i know of which does not tweak or disable search, the search functionality is hopelessly broken".

Wert
Registered User
Posts: 400
Joined: Tue Jul 03, 2001 8:33 pm

Re: Search Backend Selection

Post by Wert » Tue Jul 11, 2006 11:25 pm

Here's a question.

In terms of server load, which of the two searching methodologies is more intensive.

Which would be best in a shared hosting situation?

I'd love to try to built in mysql fulltext option, but I worry it might be harder on the server than phpBB's built in searching. But I know little about it and would appreciate some advice here.
Need good web hosting? I recommend Hostrocket.

User avatar
A_Jelly_Doughnut
Registered User
Posts: 1780
Joined: Wed Jun 04, 2003 4:23 pm

Re: Search Backend Selection

Post by A_Jelly_Doughnut » Tue Jul 11, 2006 11:41 pm

@Wert: Fulltext would be (much) less server intensive. For one thing, there is less data to crawl through. Also, the search is executed by whatever language the RDBMS is written in (most probably a form of C), so there is no overhead in interpetation of PHP.
A_Jelly_Doughnut

Wert
Registered User
Posts: 400
Joined: Tue Jul 03, 2001 8:33 pm

Re: Search Backend Selection

Post by Wert » Tue Jul 11, 2006 11:59 pm

Interesting. Thanks for the heads up AJD. I'll definitely give it a go once Olympus gets to RC status and gets a converter so I can test it with my old data.
Need good web hosting? I recommend Hostrocket.

Post Reply