[Bug] Favorites extremely slow to load #8455
Description
Describe the bug
After updating to 1.28, loading favorites is extremely slow. As you can see below, the FreshRSS interface (the requests up through favicon.ico) loaded very quickly, but took over a minute to load the list of posts.
To Reproduce
Click Favourites in the sidebar or add get=s to the URL
Expected behavior
No response
FreshRSS version
1.28.0
System information
- Database version: MariaDB 10.6
- PHP version: 8.1.33
- Installation type: Git
- Web server type: Apache 2.4
- OS: FreeBSD 14.3
- Browser: Firefox 147.0.1
Additional context
This seems to be related to #8211: running SELECT link FROM _entry WHERE is_favorite = 1 is instantaneous, but SELECT link FROM freshrssalex_entry USE INDEX (entry_feed_read_index) WHERE is_favorite = 1 takes a full minute, about the same latency as from the web interface.
rss@rss.db [rss]> EXPLAIN SELECT link FROM freshrssalex_entry WHERE is_favorite = 1;
+------+-------------+--------------------+------+---------------+-------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------------+------+---------------+-------------+---------+-------+------+-------+
| 1 | SIMPLE | freshrssalex_entry | ref | is_favorite | is_favorite | 1 | const | 175 | |
+------+-------------+--------------------+------+---------------+-------------+---------+-------+------+-------+
1 row in set (0.005 sec)
rss@rss.db [rss]> EXPLAIN SELECT link FROM freshrssalex_entry USE INDEX (entry_feed_read_index) WHERE is_favorite = 1;
+------+-------------+--------------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | freshrssalex_entry | ALL | NULL | NULL | NULL | NULL | 360025 | Using where |
+------+-------------+--------------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.001 sec)