Open xnoreq opened 3 years ago
I think I know what the issue is. I turned on logging of long queries in the database and clicked around in the Web-UI. There is one query that takes seconds. It's the special 'all articles' feed.
Here's the SQL statement:
execute pdo_stmt_0000000c: SELECT DISTINCT
date_entered,
to_char(date_entered, 'IYYY-IW') AS yyiw,
guid,
ttrss_entries.id,ttrss_entries.title,
updated,
label_cache,
tag_cache,
always_display_enclosures,
site_url,
note,
num_comments,
comments,
int_id,
uuid,
lang,
hide_images,
unread,feed_id,marked,published,link,last_read,orig_feed_id,
last_marked, last_published,
ttrss_feeds.title AS feed_title,favicon_avg_color,
content,
author,score
FROM
ttrss_entries LEFT JOIN ttrss_user_entries ON (ref_id = ttrss_entries.id)
LEFT JOIN ttrss_feeds ON (feed_id = ttrss_feeds.id)
WHERE
ttrss_user_entries.owner_uid = '2' AND
unread = true AND
true ORDER BY score DESC, date_entered DESC, updated DESC
LIMIT 200 OFFSET 0
Opening the app, the same query was executed. But not once or twice but tens of times, potentially hundreds of times (with increasing offset of course).
I think performance could be improved a lot by selecting articles per feed instead, because the queries are just a lot faster.
My experience with the app is not that great. I have about 5 categories with 10 feeds each. I have tens of thousands of unread articles, because I only mark articles read when I actually read (opened) them.
1) Starting the app it seems to start an update process of all categories & feeds that is very slow. This is very odd as in the Web-UI clicking login takes about 1s to log-in and under 1s to show all feeds with article count. In the app I have to wait about a minute for this update to finish.
2) If I don't wait and click on a category/feed then it seems to cancel this update and starts an update just of the selected feed. This update is also slow, but now "only" takes seconds. This again is odd as in the Web-UI clicking a feed loads the headlines in about a hundred ms.
3) If I don't wait for that update and click on an article then it appears to be marked as read. But going back to the feed, the "marked as read" does not stick. It only sticks once the update has gone through, so after waiting a few seconds in each feed.