Pagination in Acmlmboard is done with the typical 'LIMIT X,Y' statement. There's an issue with that and long threads, though.

Let's say the user requests page 200 of a thread. His posts per page setting is 20. Your query will look like this:

SELECT (tons of shit) FROM posts p LEFT JOIN (several other tables) WHERE t.id=1337 LIMIT 4000,20

Which basically means that MySQL will fetch 4020 rows and discard most of them.


I coded a simple solution to reduce that overhead.

function postids($table, $where) { global $sql, $loguser, $page; $ids = $sql->query("SELECT id FROM $table WHERE $where LIMIT ".(($page-1)*$loguser['ppp']).",".$loguser['ppp']); $ret = ''; while ($id = $sql->fetch($ids)) $ret .= ($ret?',':'').$id['id']; return $ret ?: 'NULL'; }

This retrieves the IDs of the posts that are going to be visible. It still has the aforementioned problem, but MySQL will read from the posts table's primary index (as opposed to reading from multiple tables), so it's much less severe.

Once you got the result of that, you feed it to the query that fetches all the remaining data.

SELECT (tons of shit) FROM posts p LEFT JOIN (several other tables) WHERE p.id IN ($result)



Another solution would consist into giving posts per-thread incrementing numbers, and base pagination on that. But this has its number of problems, especially if you find yourself wanting to merge/split threads or delete a spammer's posts. And it only works for displaying a regular thread.

____________________
Kuribo64 - melonDS

want some revolution in your coffee?

this has a flaw in that it doesn't order the selected IDs explicitly


kind of a miracle that it has worked properly at K64 for all that time

____________________
Kuribo64 - melonDS

want some revolution in your coffee?