Topic: Peculiar Query
Our forums have recently been taking a performance hit. This may be completely unrelated to PunBB, but in any case, I went digging. I soon discovered that an extraordinary number of temporary tables were being created. Not only that, but they were nearly all disk based (even with an ample amount of memory allocated to MySQL's temporary tables). I finally found the culprit to be this query within viewtopic.php:
'SELECT DISTINCT u.email, u.title, u.url, u.location, u.use_avatar, u.signature, u.email_setting, u.num_posts, u.registered, u.admin_note, p.id, p.poster AS username, p.poster_id, p.poster_ip, p.poster_email, p.message, p.hide_smilies, p.posted, p.edited, p.edited_by, g.g_id, g.g_user_title, o.user_id AS is_online FROM '.$db->prefix.'posts AS p INNER JOIN '.$db->prefix.'users AS u ON u.id=p.poster_id INNER JOIN '.$db->prefix.'groups AS g ON g.g_id=u.group_id LEFT JOIN '.$db->prefix.'online AS o ON (o.user_id=u.id AND o.idle=0) WHERE p.topic_id='.$id.' ORDER BY p.id LIMIT '.$start_from.','.$pun_user['disp_posts']
Even on my tiny test bed forum, every execution of this creates a new disk-based temporary table. Messing with indexes seems to do nothing, but removing the DISTINCT fixes things up. Looking at the query I can't really figure out why the DISTINCT is even needed. Making the query, um, non-distince doesn't seem to change the results either.
I'm still wondering why I hadn't noticed this before/why performance hadn't dragged before. It makes me think our problems aren't necessarily related to this, but creating a temporary table for every viewtopic.php seems bad. Unless, of course I've screwed up my files or databases, but I'm fairly sure my test bed is running a stock configuration.