differences between fulltext phpbb vs fulltext mysql?

[code reader]

.....(lots of stuff)

said is that if you use mysql, there is no one good reason to remain with the phpbb search.

lol, you still say there is "no good reason", but I outlined several real world scenarios and you've yet to actually answer my post with anything concrete.

i dont intend to make it a holy war, but please consider this:
the original poster that stared this thread asked for advise, for someone using mysql as their database, which search should they use: phpbb "built-in", or mysql fulltext search, (which is also "built-in" in olympus).
the system you describe, regardless of how good it actually is, is neither. it represents a very significant mod, with additional tables and indexes, cron jobs, limiting searcheable posts based on time, user groups and only you know what else.
all this stuff is not part of phpbb (or olympus) built in search, so its not really relevant to the original question.

you do have one viable point regarding the actual question being discussed: the phpbb "built-in" allows you to exclude certain forums from the search tables, while mysql fulltext does not.
the problem is, that with large enough boards, mysql fulltext is at least five times smaller and faster, (i picked it up from some stats that some users have published in a thread dedicated to this question on phpbb.com. the "x5" factor is not scientifically proven) and its possible, probable even, that this ratio becomes even larger as the board grows.

so, in order to apease the winds of war, let me rephrase:
"
if one intends to make more than 20% of their board searcheable, there isn't one good reason to prefer the phpbb built-in search over mysql fulltext search.
"

[R45]

There is one problem with that code. It destroys MySQL's ability to cache the search tables.
There are thousands of tables so it cant store any one in the memory or even just keep the file handle open as it doesnt know which one will be used.

That's the intention, it's a disk based solution as due to the size on any search index, occupying RAM is out of the question. Caching is done via reusing compiled search results, whether to go to that cache is decided by the Script not the Database.

[Cheater512]

MySQL can do more to increase speed than store the entire table in RAM.

It will keep a file open on the hard drive so when its requested again it doesnt have to find the file and open it.
You lose benefits like that.

[R45]

MySQL can do more to increase speed than store the entire table in RAM.

It will keep a file open on the hard drive so when its requested again it doesnt have to find the file and open it.
You lose benefits like that.

The overheads on opening and closing file pointers are minimal, real database bottlenecks are sorting through rows of data (moreso just finding the rows and the use of indexes). And indexes are cached in RAM, not the table, unless you use HEAPs. Though various databases use some form of table caching.

One note, the example site I gave is actually using PostgreSQL not MySQL at the moment.

[wintermute]

Do these two approaches have different footprint in the database? I mean, for example I'm very limited about dbase size and am very irritated to see search tables take more space then actual posts. Will using mySQL search method take less db space?

Many thanks,

[Cheater512]

Yes the MySQL one should use less space.

[balding_ape]

The overheads on opening and closing file pointers are minimal, real database bottlenecks are sorting through rows of data (moreso just finding the rows and the use of indexes). And indexes are cached in RAM, not the table, unless you use HEAPs. Though various databases use some form of table caching.

So it's basically like turning your search functionality into a data warehouse...trading disk space for query performance.