Done all that (the joins I mean). It's not in CVS yet, but it's live here. The messages_get function now only uses POST, USER_PEER (x2) and USER (x2). The join to the thread table now uses the previous call to thread_get() in messages.php and the other joins to the USER table have been moved to separate queries to be done only if the post needs approval or the edit message needs displaying.
I've been running a profiler against the code on my own machine (with a copy of the database from here) and noticeable bottle necks are the emoticons code which takes up to 500ms and most noticeably the threads_any_unread() function which is taking at least 1 second to complete(!), compared to the next longest running function being the RSS feed checker which takes 7ms. The threads_any_unread function is usually the cause for the load here. I've tried fiddling with it and adding indexes to the tables and rewriting the query to better use said indexes but it makes diddly squat. It even has a LIMIT clause on it now when it never used to but even that makes no difference, so if it's not the USER_THREAD table that's causing I'm stumped as to what it could be.
for ($i = 0; $i < $e_keys_size; $i++) { for ($j = 0; $j < $e_keys_size; $j++) { if ($i != $j) { if (($pos = strpos(strtolower($e_keys[$j]), strtolower($e_keys[$i]))) !== false) { $a = $e_keys[$j]; $b = $e_keys[$i]; $v = $emoticon[$a]; $a2 = urlencode($a); $a_f = preg_quote(substr($a, 0, $pos), "/"); $a_m = preg_quote(urlencode(substr($a, $pos, strlen($b))), "/"); $a_e = preg_quote(substr($a, $pos +strlen($b)), "/"); $pattern_array[] = "/". $a_f."<span class=[^>]+><span[^>]*>".$a_m."<\/span><\/span>".$a_e ."/"; $replace_array[] = "<span class=\"e_$v\" title=\"$a2\"><span class=\"e__\">$a2</span></span>"; } } } }
Yes it is the modified code.
Emoticons->Emoticons() now executes quicker than the convert function. Hooray.
Just had an hour if it being virtually dead including an error from the server which was:
Fatal error: Maximum execution time of 30 seconds exceeded in /var/www/vhosts/tehforum.co.uk/httpdocs/forum/include/emoticons.inc.php on line 103
Call Stack
# Function Location
1 {main}() /var/www/vhosts/tehforum.co.uk/httpdocs/forum/messages.php:0
2 message_display() /var/www/vhosts/tehforum.co.uk/httpdocs/forum/messages.php:386
3 message_split_fiddle() /var/www/vhosts/tehforum.co.uk/httpdocs/forum/include/messages.inc.php:618
4 emoticons->emoticons() /var/www/vhosts/tehforum.co.uk/httpdocs/forum/include/messages.inc.php:477
5 count () /var/www/vhosts/tehforum.co.uk/httpdocs/forum/include/emoticons.inc.php:103
Not sure if it's any use, but I thought I'd post it just in case :)