Notings of Attention™
Acmlmboard 2 Released
Github/GIT | @acmlmboard
Chatting Places
Discord

Affiliates
Super Mario Bros. X | Kuribo64
Views: 8,953,177
Main | FAQ | IRC chat | Memberlist | Active users | Latest posts | Stats | Ranks | Online users | Search
03-29-24 09:16 AM
Guest: Register | Login

0 users currently in AcmlmBoard Developer Zone | 1 guest | 2 bots

Main - AcmlmBoard Developer Zone - Pagination optimizations
Next newer thread | Next older thread


Arisotura
Posted on 03-12-15 10:54 PM, in Link | ID: 82914
Developer
pancakes
Level: 83


Posts: 713/1868
EXP: 5395836
Next: 36385

Since: 01-05-12
From: France

Last post: 655 days
Last view: 194 days
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?

Arisotura
Posted on 10-01-15 01:15 AM, in Link | ID: 87569
Developer
pancakes
Level: 83


Posts: 884/1868
EXP: 5395836
Next: 36385

Since: 01-05-12
From: France

Last post: 655 days
Last view: 194 days
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?

Next newer thread | Next older thread
Main - AcmlmBoard Developer Zone - Pagination optimizations


Acmlmboard v2.5.5 (10/04/2020)
© 2005-2024 Acmlm, Emuz, et al.

Page rendered in 0.033 seconds. (755KB of memory used)
MySQL - queries: 63, rows: 474/507, time: 0.026 seconds.