25.0.0-alpha3: missing index on feeds(deleted_at)

[Ost3rbaer]

IMPORTANT

Read and tick the following checkbox after you have created the issue or place an x inside the brackets ;)

• I have read the CONTRIBUTING.md and followed the provided tips
• I accept that the issue will be closed without comment if I do not check here
• I accept that the issue will be closed without comment if I do not fill out all items in the issue template.

Explain the Problem

What problem did you encounter?
news creates queries that can not be properly optimised by MySQL:

mysql> explain SELECT `items`.* FROM `oc_news_items` `items` INNER JOIN `oc_news_feeds` `feeds` ON items.feed_id = feeds.id WHERE (feeds.user_id = 'smurf') AND (feeds.deleted_at = 0) AND (items.unread = '1') ORDER BY `items`.`id` DESC LIMIT 40;
+----+-------------+-------+------------+------+---------------------------------------------------------------------+---------------------------+---------+--------------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys                                                       | key                       | key_len | ref                      | rows | filtered | Extra                                        |
+----+-------------+-------+------------+------+---------------------------------------------------------------------+---------------------------+---------+--------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | feeds | NULL       | ALL  | PRIMARY,news_feeds_user_id_index                                    | NULL                      | NULL    | NULL                     |   18 |    10.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | items | NULL       | ref  | news_items_unread_feed_id,news_items_unread_id,IDX_EA56D89651A5BC03 | news_items_unread_feed_id | 9       | const,nextcloud.feeds.id | 9792 |   100.00 | NULL                                         |
+----+-------------+-------+------------+------+---------------------------------------------------------------------+---------------------------+---------+--------------------------+------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.30 sec)

this results in high load on the DB when there are many (>100k) news items even if there are only a few dozen feeds. The problem is the missing index on feeds.deleted_at.

Steps to Reproduce

Explain what you did to encounter the issue
just open the news page and monitor the server load/query with mysqladmin processlist. Initial queries can last up to 20 minutes.

System Information

• News app version: 25.0.0-bet3
• Nextcloud version: 28.0.1.1 from "official" docker image
• Cron type: (system cron/python updater/...) cron
• PHP version: 4.2.14
• Database and version: MySQL 8.0.35
• Browser and version: Firefox 121.0/Win64
• OS and version: Debian 12 (in docker image) on OpenSuSE/Leap 15.5 (docker host)

The issue is resolved by adding an index on oc_news_feeds.deleted_at:

mysql> alter table oc_news_feeds add key  `news_feeds_deleted_at_index` (`deleted_at`);
Query OK, 0 rows affected (2.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT `items`.* FROM `oc_news_items` `items` INNER JOIN `oc_news_feeds` `feeds` ON items.feed_id = feeds.id WHERE (feeds.user_id = 'smurf') AND (feeds.deleted_at = 0) AND (items.unread = '1') ORDER BY `items`.`id` DESC LIMIT 40;
+----+-------------+-------+------------+--------+---------------------------------------------------------------------+---------+---------+-------------------------+------+----------+----------------------------------+
| id | select_type | table | partitions | type   | possible_keys                                                       | key     | key_len | ref                     | rows | filtered | Extra                            |
+----+-------------+-------+------------+--------+---------------------------------------------------------------------+---------+---------+-------------------------+------+----------+----------------------------------+
|  1 | SIMPLE      | items | NULL       | index  | news_items_unread_feed_id,news_items_unread_id,IDX_EA56D89651A5BC03 | PRIMARY | 8       | NULL                    |   80 |    50.00 | Using where; Backward index scan |
|  1 | SIMPLE      | feeds | NULL       | eq_ref | PRIMARY,news_feeds_user_id_index,news_feeds_deleted_at_index        | PRIMARY | 8       | nextcloud.items.feed_id |    1 |   100.00 | Using where                      |
+----+-------------+-------+------------+--------+---------------------------------------------------------------------+---------+---------+-------------------------+------+----------+----------------------------------+
2 rows in set, 1 warning (0.00 sec)

[SMillerDev]

Could you make a pull request to remedy that?

[Ost3rbaer]

Could you make a pull request to remedy that?

Unfortunately I know nothing about how nextcloud handles it's database definitions/migrations. Ideally that index would be handled the same way as other missing indices that are listed after and update/upgrade and get resolved with occ db:add-missing-indices. I just have no clue how to achieve that. The online documentation only refers to how to run the command - not on how to define database indices.

[Grotax]

I will check how we can do that :)