differences between fulltext phpbb vs fulltext mysql?

[LucidParody]

What's the difference? Is one faster then the other?

[the_dan]

The phpBB search engine uses a word index built up when posts are made. This requires more queries, processing time, and storage space, but works on older versions of MySQL, and on other DMBS'.

The MySQL fulltext engine creates fulltext indexes on certain database fields, which requires extra storage space, but lessens the complexity of the posting system, and is probably quicker and more efficient. It has the major disadvantage that it stops working as soon as you move off MySQL.

[dhn]

It has the major disadvantage that it stops working as soon as you move off MySQL.

In that case it is just a matter of rebuilding the index.

BTW, if there are a few people around that are interested in helping out, new search "plugins" for using the built-in indexing of other database system could be available soon.

[LucidParody]

The phpBB search engine uses a word index built up when posts are made. This requires more queries, processing time, and storage space, but works on older versions of MySQL, and on other DMBS'.

The MySQL fulltext engine creates fulltext indexes on certain database fields, which requires extra storage space, but lessens the complexity of the posting system, and is probably quicker and more efficient. It has the major disadvantage that it stops working as soon as you move off MySQL.

So assuming you were planning to stick with mySQL, the latter fulltext search would be the better option?

[the_dan]

I would think so.

[R45]

It depends on the size of your board. If you have a very large board, you might be inclined to use phpbb fulltext and scale down the system, as the mysql fulltext doesn't scale well.

I would be willing to write a tsearch2 plugin (PostgreSQL's fulltext column type) if the dev team is interested.

[LucidParody]

It depends on the size of your board. If you have a very large board, you might be inclined to use phpbb fulltext and scale down the system, as the mysql fulltext doesn't scale well.

I would be willing to write a tsearch2 plugin (PostgreSQL's fulltext column type) if the dev team is interested.

Define large?

[R45]

Large is variable, depending on the size of your posts. For example, a forum with 2 million posts but mostly 1 line chat styled contents is much less stressful than 50,000 paragraph full posts. Maybe based on a filesize, I'd say a posts table near 1GB and beyond may start to hit performance problems with MySQL's fulltext.

[LucidParody]

Large is variable, depending on the size of your posts. For example, a forum with 2 million posts but mostly 1 line chat styled contents is much less stressful than 50,000 paragraph full posts. Maybe based on a filesize, I'd say a posts table near 1GB and beyond may start to hit performance problems with MySQL's fulltext.

Oh nice. I'm only at about 120mb so I doubt I will have that problem. I figure at about 500mb, I'll start pruning posts anyway [out of general community/chit-chat first ].

[naderman]

I would be willing to write a tsearch2 plugin (PostgreSQL's fulltext column type) if the dev team is interested.

You're welcome to create a new search plugin, and we hope to see a lot more once phpBB3 is released. Hopefully a lot of users will profit from the improvements such a database specific search plugin can achieve.