Skip to main content ↑ ↓
Topic: Making pagination faster (Read 1957 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

Making pagination faster

On a drive today I was thinking of how to make pagination faster. Generally, I was thinking of how to improve performance for any large forum. What I was thinking is that we should add a field to the messages table, reply_num. The first post in a topic will have a reply_num of 0, every subsequent post will have an ascending number. Then, all we have to do for pagination is do some simple math.

Of course, if you require that posts be approved it doesn't really work. Large forums don't usually require that posts be approved. Really, most forums don't do that, sans a couple of boards. In that case, it still provides some good info for the theme though.

Since it is extremely rare that you have more than 65k messages in a topic, it would be a smallint. Then that only requires 2 bytes per message.

It adds some update queries any time you remove a message from a topic where id_last_msg != id_msg (of the message you're removing).

Re: Making pagination faster

Reply #1 –

You mean which messages go into a page?
That would require a id_reply > XXX < id_reply in the query, right? As far as I remember this kind of comparison makes queries less happy.

Then why not directly put the page number? That way if you want page 6 you do page = 6 in the WHERE and that's all. No?

Two small problems:
1) yes, removing a message would be kind of a nightmare in terms of updates using pages,
2) that would mean fixed pagination for anyone (at the moment each member can decide different number of messages per page... <= that I wouldn't be against the removal TBH),
Bugs creator.
Features destroyer.
Template killer.

Re: Making pagination faster

Reply #2 –

Example: 10 posts per page, page 5 of 10, id_topic = 23

SELECT * FROM messages WHERE id_topic = 23 AND reply_num BETWEEN 50 AND 60 OR reply_num = 0

That gets all of the messages for the page and the first message for the page title. It isn't fixed like a page number would be. The only time messages need to be updated is if a message is removed from the "middle" of the topic. Even if a message is removed from the middle, it is usually towards the end of the topic.

Re: Making pagination faster

Reply #3 –

While handling removal one need to handle the split functionality too, but overall the idea sounds pretty interesting.

Re: Making pagination faster

Reply #4 –

Quote from: groundup – On a drive today I was thinking of how to make pagination faster. Generally, I was thinking of how to improve performance for any large forum. What I was thinking is that we should add a field to the messages table, reply_num. The first post in a topic will have a reply_num of 0, every subsequent post will have an ascending number. Then, all we have to do for pagination is do some simple math.

Of course, if you require that posts be approved it doesn't really work. Large forums don't usually require that posts be approved. Really, most forums don't do that, sans a couple of boards. In that case, it still provides some good info for the theme though.

Since it is extremely rare that you have more than 65k messages in a topic, it would be a smallint. Then that only requires 2 bytes per message.

It adds some update queries any time you remove a message from a topic where id_last_msg != id_msg (of the message you're removing).


When you're driving, you're supposed to concentrate on the road, not thinking about making pagination faster!

but nice feature request tho. :P