#3185 - Pagination indexing table specifically for forum topics
-
By Chris Graham
- Added
- 2 views
| Identifier | #3185 |
|---|---|
| Issue type | Feature request or suggestion |
| Title | Pagination indexing table specifically for forum topics |
| Status | Open |
| Tags |
Type: Performance (custom) |
| Handling member | Deleted |
| Addon | cns_forum |
| Description | Add 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. |
| Steps to reproduce | |
| Related to | |
| Funded? | No |
The system will post a comment when this issue is modified (e.g., status changes). To be notified of this, click "Enable comment notifications".
Comments
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 #3958).
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.