View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0003185||Composr||cns_forum||public||2017-04-05 12:03||2019-11-24 21:17|
|Reporter||Chris Graham||Assigned To|
|Fixed in Version|
|Summary||0003185: Pagination indexing table specifically for forum topics|
|Description||Add a new table, f_topicview_pagination, with these fields:|
This would help with performance on large topics for these cases:
1) calculating what the last page is when jumping to the most recent post
2) calculating the appropriate page when jumping to a specific post in a topic
3) moving around in pagination to any arbitrary jump page
The problem is that MySQL uses a b-tree index, so while we of course index posts in a topic by timestamp, we can't jump to a position along that index without it having to traverse the b-tree. This is a common problem. It only really manifests when you have 1000s of posts in a topic.
We will have to write code to maintain this whenever posts are validated or deleted. Inline-personal-posts would show additional to the posts within the range (i.e. if you had an inline personal post then you might see 21 on a screen rather than 20). This is necessary to stop us having to have a separate index structure for each member.
We will need to limit the "per page" selectors to multiples of the default per page, and then we can cleverly merge in ranges from multiple pages.
We will need to be able to dynamically rebuild the table if it gets flushed out.
Also for pagination in a forumview (less important, could perhaps be put into a separate issue):
Add a new table, f_forumview_pagination, with these fields:
Maintenance of this table will be a bit tricker, as it needs adjusting each time a topic is added (unlike for a topicview, latest displays first). We should be able to do some kind of roll-along.
|Tags||ocProducts client-work (likely), Type: Performance|
|Time estimation (hours)||20|
We may be able to implement this by wrapping the database layer.
If it can find a pagination index record covering a pagination, it would fall back to regular use of offsets. This is important, as we can't immediately rebuild the index every time a post is added/deleted - we can do it in the background using a Cron hook (which depends on 0003958).
It's also important to consider different sort orders and filters that may be active. There may need to be more fields and more indexes for the index table.
We should also try and cache counts for particular paginations.
|2017-04-05 12:03||Chris Graham||New Issue|
|2017-04-05 12:04||Chris Graham||Tag Attached: Type: Performance|
|2017-04-05 12:04||Chris Graham||Relationship added||related to 0001854|
|2017-07-05 00:09||Chris Graham||Tag Attached: ocProducts client-work (likely)|
|2019-11-18 23:25||Chris Graham||Summary||Pagination indexing table (performance) => Pagination indexing table|
|2019-11-18 23:29||Chris Graham||Note Added: 0006161|
|2019-11-19 01:16||Chris Graham||Relationship added||related to 0003958|
|2019-11-19 01:16||Chris Graham||Note Edited: 0006161||View Revisions|
|2019-11-24 21:17||Chris Graham||Summary||Pagination indexing table => Pagination indexing table specifically for forum topics|
|2019-11-24 21:17||Chris Graham||Note Edited: 0006161||View Revisions|