MyBB Community Forums

This is primarily about MyBB 1.6, but probably applies to 1.8 as well.
Also, I'm focusing on the MySQL database, and some things here may not apply to other systems.
These are just some slow queries that I've noticed recently.

---

A number of MyBB queries use "WHERE LOWER(username) = ...". This causes a full scan over the users table (read: slow query). The default MySQL collation ignores case, so the LOWER() is unnecessary and allows the index to be used.
However, I don't know whether the other supported database systems do this, and it may be possible that some MySQL collations are case sensitive.

I don't know if this wants to be solved or how you'd like to do it, but putting it out there.


Also, the dotfolders query on forumdisplay could use some improvement.

$query = $db->simple_select("posts", "tid,uid", "uid='{$mybb->user['uid']}' AND tid IN ({$tids}) {$visibleonly}");

An index on uid,tid on the posts table could help.
I haven't checked, but maybe a GROUP BY tid,uid clause may make it faster, since you don't need _every_ post made by the user.

The first one is already reported: [Issue #349]

About the second one: we've added some indexes in 1.8, they should be one of them

Ah, that's good to hear.
Thanks for the info.

The second one is true for 1.8 only for the index, not the group by clause. The first one I'm unsure if will be "fixed" on both or altogether.

GROUP BY doesn't really seems to help that much in my tests, so unless it depends on configuration/installations/whatnot I suppose it is not making it in..

GROUP BY tends to be slow, at least for large result sets. I haven't actually tested though since I no longer have a large database to work with.