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

Affiliates
Super Mario Bros. X | Kuribo64
Views: 5,343,747
Main | FAQ | IRC chat | Memberlist | Active users | Latest posts | Calendar | Stats | Ranks | Online users | Search
07-23-18 09:32 AM
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: 71


Posts: 713/1597
EXP: 3120477
Next: 46637

Since: 01-05-12
From: France

Last post: 7 hours
Last view: 7 hours
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
Acmlmboard has... side effects
Level: 71


Posts: 884/1597
EXP: 3120477
Next: 46637

Since: 01-05-12
From: France

Last post: 7 hours
Last view: 7 hours
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 (5/28/2018)
© 2005-2018 Acmlm, Emuz, et al.

Page rendered in 0.029 seconds. (701KB of memory used)
MySQL - queries: 59, rows: 440/473, time: 0.018 seconds.