Points of Required Attention™
Acmlmboard 2 Released
Github/GIT | @acmlmboard | IRC: #abdev
Chatting Places
IRC: #kafuka | Discord

Affiliates
Super Mario Bros. X | Kuribo64
Views: 4,787,043
Main | FAQ | IRC chat | Memberlist | Active users | Latest posts | Calendar | Stats | Ranks | Online users | Search
01-22-18 10:02 PM
Guest: Register | Login

0 users currently in AcmlmBoard Developer Zone | 1 guest

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


StapleButter
Posted on 03-12-15 10:54 PM Link | ID: 82914
Developer
Acmlmboard has... side effects
Level: 69


Posts: 713/1548
EXP: 2862709
Next: 7034

Since: 01-05-12
From: France

Last post: 4 days
Last view: 2 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 - we hack shit

StapleButter
Posted on 10-01-15 01:15 AM Link | ID: 87569
Developer
Acmlmboard has... side effects
Level: 69


Posts: 884/1548
EXP: 2862709
Next: 7034

Since: 01-05-12
From: France

Last post: 4 days
Last view: 2 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 - we hack shit

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


Acmlmboard v2.5.3 (1/7/2018)
© 2005-2018 Acmlm, Emuz, et al.

Page rendered in 0.024 seconds. (672KB of memory used)
MySQL - queries: 59, rows: 509/533, time: 0.013 seconds.