Fulltext native vs Fulltext mysql

Do not post support requests, bug reports or feature requests. Discuss phpBB here. Non-phpBB related discussion goes in General Discussion!
Get Involved
Post Reply
Ron2K
Registered User
Posts: 247
Joined: Sun May 25, 2003 6:10 pm
Location: Cape Town, South Africa
Name: Kieron Thwaites
Contact:

Fulltext native vs Fulltext mysql

Post by Ron2K » Tue Jun 17, 2008 8:52 am

Hi all,

I've taken over a PHP-Nuke instance with 910,000 posts, 27,000 topics and 12,000 users. I'm looking to move away from that rotten pile of spaghetti and convert it to Joomla + phpBB 3.

I was just wondering: for a board of that size, which of the two search backends would be the best to use? I've seen users here and on area51 stating that they believe fulltext native to be the better of the two search backends, but I've never actually seen any reasons for these opinions.

I'd really appreciate any advice in this regard. :)

User avatar
Eelke
QA Team
Posts: 2903
Joined: Thu Dec 20, 2001 8:00 am
Location: NL, Bussum
Name: Eelke Blok
Contact:

Re: Fulltext native vs Fulltext mysql

Post by Eelke » Tue Jun 17, 2008 9:17 am

What makes you think you'll get anything more useful now than what has been said in all the previous topics about this? :) My gut feeling (so not "compromised" by any factual information whatsoever) tells me that the full text native is quirky in the way it works (looks a lot like a "best effort" kind of thing), so that any built-in system the database would offer is bound to be better. At least the amunt of storage used is supposed to be better with fulltext mysql (there is a recent thread that suggest some improvements to even get rid of one of the indexes).
Last edited by karlsemple on Tue Jun 17, 2008 9:20 am, edited 1 time in total.
Reason: Added link

CardsFanInChiTown
Registered User
Posts: 59
Joined: Mon May 15, 2006 5:03 am

Re: Fulltext native vs Fulltext mysql

Post by CardsFanInChiTown » Tue Jun 17, 2008 3:12 pm

I have a phpbb board with 500K posts and I recently switched to Fulltext mysql. It's faster and uses less DB space.

User avatar
Phil
Former Team Member
Posts: 10403
Joined: Sat Nov 25, 2006 4:11 am
Name: Phil Crumm
Contact:

Re: Fulltext native vs Fulltext mysql

Post by Phil » Tue Jun 17, 2008 7:35 pm

I'm not an expert but I recall a thread on Area51 (I'll look in a bit to see if I can dig it up) that stated that fulltext MySQL is a bit faster, but it takes up more space in the database and doesn't scale as well as fulltext native. This was awhile back ago (I'm thinking about a year or so), so this may have changed by now.
Moving on, with the wind. | My Corner of the Web

User avatar
A_Jelly_Doughnut
Former Team Member
Posts: 34457
Joined: Sat Jan 18, 2003 1:26 am
Location: Where the Rivers Run
Contact:

Re: Fulltext native vs Fulltext mysql

Post by A_Jelly_Doughnut » Tue Jun 17, 2008 11:48 pm

I can clarify the problems with Fulltext MySQL on large boards. MySQL only supports fulltext indexing with MyISAM tables. MyISAM uses table-level locking (so only one operation can happen on a table at any given moment). This can bog down significantly on posts with dozens of simultaneous users.

Large boards like phpBB.com use InnoDB tables, which do not support Fulltext indexes. But they does offer the performance boost of row-level locking.

For boards of a million posts or so, the best search mechanism is probably Sphinx, available at http://code.phpbb.com. It is not supported to the same level as the rest of phpBB, though.
The next-best option is Fulltext Native because it does not require MyISAM tables.

MySQL 5 is more efficient at fulltext searching than phpBB is, so for moderately sized boards running on MySQL 5, MySQL fulltext is probably the best method.
A Donut's Blog
"Bach's Prelude (Cello Suite No. 1) is driving Indiana country roads in Autumn" - Ann Kish

Post Reply

Return to “phpBB Discussion”