2005-11-07, 03:14 PM
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:
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
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