Elk Development => Feature Discussion => Topic started by: Joshua Dickerson on August 19, 2014, 01:31:32 am
Title: Making pagination faster
Post by: Joshua Dickerson on August 19, 2014, 01:31:32 am
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).
Title: Re: Making pagination faster
Post by: emanuele on August 19, 2014, 04:50:30 am
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),
Title: Re: Making pagination faster
Post by: Joshua Dickerson on August 19, 2014, 03:38:48 pm
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.
Title: Re: Making pagination faster
Post by: Joker™ on August 21, 2014, 11:02:18 am
While handling removal one need to handle the split functionality too, but overall the idea sounds pretty interesting.
Title: Re: Making pagination faster
Post by: Mstcool on August 21, 2014, 01:10:55 pm
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!