DB times out with many news items

[unterkomplex]

Hi,

I wanted to share this in case it helps anyone and maybe someone even has an idea how to improve the performance

My Nextcloud instance is running on a rather low-powered machine. I am on Nextcloud 30, latest alpha12 of the News app and have ~4k unread feeds / 86k total feeds

I am using Fiery Feeds to connect to the News app. The initial sync works fine, but when I try to refresh the feeds the request times out. I checked on the server and it looks like MariaDB takes ~40 seconds to respond to this query

SELECT `items`.* FROM `oc_news_items` `items` INNER JOIN `oc_news_feeds` `feeds` ON items.feed_id = feeds.id WHERE (feeds.user_id = 'news') AND (feeds.deleted_at = 0) ORDER BY `items`.`id` DESC LIMIT 500;

If I analyze that query it shows that it does use the index on the items table, but still takes a very long time
MariaDB [nextcloud]> analyze SELECT items.* FROM oc_news_items itemsINNER JOINoc_news_feeds feeds ON items.feed +------+-------------+-------+------+--------------------------------------------------------------+----------------------+---------+--------------------+------+---------+----------+------------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+------+--------------------------------------------------------------+----------------------+---------+--------------------+------+---------+----------+------------+----------------------------------------------+ | 1 | SIMPLE | feeds | ALL | PRIMARY,news_feeds_user_id_index,news_feeds_deleted_at_index | NULL | NULL | NULL | 123 | 123.00 | 100.00 | 100.00 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | items | ref | IDX_EA56D89651A5BC03 | IDX_EA56D89651A5BC03 | 8 | nextcloud.feeds.id | 473 | 1072.47 | 100.00 | 100.00 | | +------+-------------+-------+------+--------------------------------------------------------------+----------------------+---------+--------------------+------+---------+----------+------------+----------------------------------------------+ 2 rows in set (39.643 sec)

I think the easy way to fix it would be to reduce the Maximum read count per feed in the administration settings. Does anyone have a way that improves the MariaDB performance for that query so that the queries return a result in time?