MyBB Community Forums

I have been administrating rather large MyBB-based forum for year or so. At some point (maybe it was from version RC-something, don't remember that well) we noticed a sudden drop of performance when using forum's search function. With search execution time being barely tolerable (max 20-30 sec), I didn't pay much attention to the issue. But today I finally decided to use my post-lunch rest break for poking around in MyBB search mechanism, maybe introducing some full-text indexes or something.

After making full text index work (I can share the hacked code, but this is actually another issue) it only helped performance by some 50%. Still a bit too slow. So I commented out the queries one by one, to find the bastard. Successfully:

$query = $db->query("SELECT p.tid FROM ".TABLE_PREFIX."posts p, ".TABLE_PREFIX."threads t WHERE $wheresql $permsql");
	$results = $db->num_rows($query);

	if(!$results)
	{
		error($lang->error_nosearchresults);
	}

Basically it creates a HUGE recordset (I managed to break 4 000 000 record barrier with 2 quick shots), stores it to PHP memory and calls a simple is_defined() function to process the recordset. Just to display a simple error message, because as far as I can understand, $result goest to trash bin after block of code quoted above.

I commented the whole thing out as a quick hack, we rarely get 0 result set anyway.

As a result, 30 sec execution time was reduced to 1-2 seconds

Actually the whole search thing could benefit hugely from using MySQL's built-in fulltext index functionality.

Jesper Saron
forum.automoto.ee

We'll be looking into changing the search to use our own word based index search or MySQL fulltext if your server supports it in 1.1 - we've only just began looking into fulltext searching and boolean searching ourselves.

Thanks for your comments,

I have checked version 1.03 and this issue is still there, even though a bit transformed. At line 119 of search.php there is "$resultcount = $db->num_rows($query);" which should be optimized - why do you fetch the whole result set into PHP just to count it? And to make it even worse you ask the database to sort the result set - that's simply terrible. You should speed everything up by asking the database to do the counting for you - "SELECT COUNT(*)" is what should be done.

And I completely agree that the whole search function deserves to be rewritten. At this moment the database table mybb_posts is scanned without any index, which is not acceptable for a large forum. Using the fulltext features of MySQL seems to be fast and easy to implement.

That's because 1.1/1.2 hasn't been released yet. 1.0x releases are just security vulnerability patches.

as another member and administrator of that board think its the only board that is originally converted from xmb > devbb > mybb



anyway is that issue been fixed in new release?

Yes, if your server supports MySQL's fulltext searching then you can choose to use it with MyBB.

Im still having real problems with search issue. It takes my quite powerful server down if there are too many search querys... we fixed some stuff and now its better but still...

Think my board is the largest mybb borad around becouse seems like other boards are okay and dont have that much traffic and search uses. My forum containts loads of useful information and even 5 years old threads are still popular reading material...

My board has only few hacks but what about this msn patch forum? It might be the biggest, but how much is there original stuff left?

anyway... my adivce is to put more work into making forum faster rather than making new template sets...


mybb fan nr1

The Messenger Plus! forums are running an older version of MyBB than the latest. The search script there is sort of similar to how the new search engine works in MyBB 1.2 (non-fulltext)

Have you tried setting the search method to "Fulltext" as long as your server supports it? It'll lift a hell of a load off MySQL for searching as well as provide better search capability support (complex search queries etc).

That being said, MyBB is fast. Have you got any table indexes created (speeds things up as well), or your server optimized?