MyBB Community Forums

Currently the way MyBB implements pagination is by using LIMIT start , perpage.  While smaller forums this isn't a huge issue, for large ones it can be a big performance hit because it results in full table scans.  My suggestion is to improve the where clause and use just limit.

Ex.

if(isset($mybb->input['pid']))
{
$where .= " AND p.pid > " . $mybb->get_input('pid', MyBB::INPUT_INT) . ";
}
$query = $db->query("SELECT p.*, t.*, u.*, ug.* FROM " . TABLE_PREFIX . "posts ... $where ORDER BY p.pid ASC LIMIT $perpage");

I'm also suggesting using pid instead of dateline because in theory the dateline should always be getting bigger since drafts are stored with a visible value of -1 or -2 ( can't remember which ).  Being able to use the primary key while having Innodb makes the query execute faster.  Speaking of Innodb, we should seriously consider having most of the tables be Innodb instead of MyISAM because data updates frequently causing the entire table to lock.

I full heartedly agree on improving query efficiency as long as the query remains (relatively) easy to understand. Your example falls into this category. When viewing categories on MediaWiki, it uses a parameter for the first category to show instead of page numbers.

I like Innodb more and more, especially since fulltext index support was added a few years back.