@maiyannah In the end I don't think I succeeded in my attempts at optimising the SELECT at lib/inboxnoticestream.php.
But on !loadaverage another thing actually did get a good result: the Compressed raw format on the "notice" table.
Seems counter-intuitive that compression improves performance but, on the other hand, that makes the amount of data simultaneously in RAM a lot larger, so here we are.