Attention!!™
SMB3 Hacking Forum Opened!
Information needed!
If you have any useful data on hacking SMB3, please post it in the this stickied thread in the new SMB3 Forum. We are also looking for someone to help run a SMB3 hacking Wiki.
Join the Kafuka Discord! (Click here)
Views: 5,727,003
Main | FAQ | IRC chat | Memberlist | Active users | Latest posts | Calendar | Stats | Ranks | Online users | Search
11-21-18 07:54 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
there was a girl
Level: 72


Posts: 713/1637
EXP: 3319673
Next: 4089

Since: 01-05-12
From: France

Last post: 6 days
Last view: 6 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
obsolete advert is obsolete

StapleButter
Posted on 10-01-15 01:15 AM Link | ID: 87569
Developer
there was a girl
Level: 72


Posts: 884/1637
EXP: 3319673
Next: 4089

Since: 01-05-12
From: France

Last post: 6 days
Last view: 6 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
obsolete advert is obsolete

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


Acmlmboard v2.5.4 (10/13/2018)
© 2005-2018 Acmlm, Emuz, et al.

Page rendered in 0.030 seconds. (603KB of memory used)
MySQL - queries: 55, rows: 440/473, time: 0.017 seconds.