Load time with 1 million activity entries and 1 million comment entries should be less than 100ms at this point or at least is in my tests. Previously such an environment would take around 2s. It's mostly a 2 part issue. The prefetching and the pagination.

The prefetching checks to see if there's comment, likes, or tags for an activity entry as fast as possible. This lets CB Activity know "I should check for comments and display them". So for example if you display 15 activity entries and only 3 have comments it doesn't bother querying for comments except for those 3 due to the prefetch. This basically is to favor doing a 1ms query over a slower one.

The second part, which isn't fixed yet and can be tracked below, is the pagination. It's still old school. Meaning it does a count query to determine if there's more than 1 page then does the select query for the actual display. So you get double query on initial view. All of that is pointless in a stream environment since we don't care how many there are. All we need to know is PAGE LIMIT + 1. If the amount of rows returned is greater than PAGE LIMIT we know we've another page. The second issue with the paging is it's offset based so it's doing LIMIT 0, 15. This is fine below 100,000 rows but if you have something like LIMIT 100000, 15 it gets exponentially slower. I'll be reviewing timestamp offset based paging which offsets in the WHERE statement by the last seen timestamp then just grabbing the next 15 rows, which should scale infinitely.

forge.joomlapolis.com/issues/7503

The goal after fixing the paging issues is to hit around 10-20ms for a 1 million row database. I don't think I can really get it any better at that point without maybe a full storage rewrite to use relationship tables and transition to a push based usage, but I doubt anyone would like that since that massively increases database storage slowing down write access but boosting read access.

As it stands though performance is excellent with a large amount of data so you shouldn't really have any noticeable performance issues at this point having fixed the prefetch queries.