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
Search Backend Selection
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!
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!
Re: Search Backend Selection
Come on, there has to be someone out there that knows the difference...
-
- Registered User
- Posts: 198
- Joined: Fri Jan 02, 2004 3:44 pm
- Location: United Kingdom
- Contact:
Re: Search Backend Selection
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
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. ||新熱です
The NeoThermic.com... a well of information. Ask me for the bit bucket so you can drink its goodness. ||新熱です
Re: Search Backend Selection
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
-
- Registered User
- Posts: 653
- Joined: Wed Sep 21, 2005 3:01 pm
Re: Search Backend Selection
in addition to being smaller and faster, native mysql fulltext has these added advantages:
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.
- 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.
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.
-
- Registered User
- Posts: 198
- Joined: Fri Jan 02, 2004 3:44 pm
- Location: United Kingdom
- Contact:
Re: Search Backend Selection
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. ||新熱です
The NeoThermic.com... a well of information. Ask me for the bit bucket so you can drink its goodness. ||新熱です
-
- Registered User
- Posts: 653
- Joined: Wed Sep 21, 2005 3:01 pm
Re: Search Backend Selection
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".
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".
Re: Search Backend Selection
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.
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.
- A_Jelly_Doughnut
- Registered User
- Posts: 1780
- Joined: Wed Jun 04, 2003 4:23 pm
Re: Search Backend Selection
@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
Re: Search Backend Selection
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.