View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 3185 | Composr | cns_forum | public | 2017-04-05 12:03 | 2021-04-06 01:30 |
| Reporter | Chris Graham | Assigned To | Guest | ||
| Priority | normal | Severity | feature | ||
| Status | new | Resolution | open | ||
| Summary | 3185: Pagination indexing table specifically for forum topics | ||||
| 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. | ||||
| Tags | Type: Performance | ||||
| Attach Tags | |||||
| Time estimation (hours) | 20 | ||||
| Sponsorship open | |||||
|
|
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 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. |
| 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 1854 |
| 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 3958 |
| 2019-11-19 01:16 | Chris Graham | Note Edited: 0006161 | |
| 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 | |
| 2021-04-06 01:30 | Chris Graham | Tag Detached: ocProducts client-work (likely) |
