Thank-you very much for the reply. So you are confident it isn't the mod or PHPBB? I have been hearing a great deal from my host/developer who thinks it is one of the two or a combination - I would say they are very anti-phpbb.
phpbb may well be involved, but it's not the reason. Developers who don't investigate real reasons often point to a scapegoat.
I think I know how to optimise it, the same was as you would repair a table, I can optimise it? Select the table and optimise?
What other tools do you suggest?
I am uncomfortable suggesting DBA tasks to you over the net, being that neither of us are DBAs.
But if you are determined to go it on your own see here:
It's so frustrating, I know something is so wrong somewhere and it's hard not having the words to relay the problem... I am just sick of the problem as are the members and this table shouldn't be corrupting all the time. The guys on PHPBB have replied the last 24 hours and feel that it's a DB issue - the developer doesn't understand why it has 5 million rows and thinks its far too excessive? Can't seem to get much more than that.
The developer has a small point, phpbb's wordmatch is made in a way that is very database intensive.
However, that is just a matter of economy, not the cause of the problem. Of course, with greater economy you give fewer chances for said problems to occur.
Can the search utility be restricted so the table wont need to be so big?
Yes, however this can mean complicated modification of phpbb and locking yourself into a method (i.e. mysql fulltext search) that won't allow you to easily go back.
Or is it not necessary? I've noticed lots of large forums, phpbb or others, seem to have troubles with their search and some of my competitors with various forums have been going to google to have site searches instead.
The indexing of so many words is always a bit problematic. But Google will never be as fresh as an index that's always up to date. The problem is that keeping it up to date is database intensive.
Google doesn't do live indexing, it has other machines building indexes than the one you use to search and then rolls out the updates.
phpbb does not have this kind of scalability, nor do any other forum software packages.