View Issue Details

IDProjectCategoryView StatusLast Update
0003185Composrcns_forumpublic2019-11-24 21:17
ReporterChris GrahamAssigned To 
Severityfeature 
Status non-assignedResolutionopen 
Product Version 
Fixed in Version 
Summary0003185: Pagination indexing table specifically for forum topics
DescriptionAdd a new table, f_topicview_pagination, with these fields:
 - topic_id
 - per_page
 - pagination_number
 - timestamp_from
 - timestamp_to
 - id_from
 - id_to

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:
 - forum_id
 - per_page
 - pagination_number
 - timestamp_from
 - timestamp_to
 - id_from
 - id_to

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.
TagsocProducts client-work (likely), Type: Performance
Attach Tags
Time estimation (hours)20
Sponsorship open

Relationships

related to 0001854 non-assigned Support keyset pagination 
related to 0003958 non-assigned Opportunistic scheduler 

Activities

Chris Graham

2019-11-18 23:29

administrator   ~0006161

Last edited: 2019-11-24 21:17

View 3 revisions

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.

Issue History

Date Modified Username Field Change
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