SQL improvment

[ldidry]

Hello,

Informations : TTRSS 16.8 with PostgreSQL 9.1 and PHP7.

I manage a TTRSS instance with 250 users and a SQL query in the update_feedbrowser_cache function (include/rssfuncs.php) is taking too much time and CPU. I managed to modify the query in order to reduce the query time from 1,387,000ms (yep, really, to typo, no mistake, it's more than 20 minutes and the PostgreSQL thread takes 100% of a CPU while it's querying) to 380ms.

I know that the query is taking a long time because of my lot of users and feeds (9609 feeds in ttrss_feeds table) and that TTRSS is not designed to be used at such scale but since I managed to have a lot more efficient query, it can be useful to everyone, don't you think? (By the way, even if TTRSS is not designed to be used with 250 users, it works well )

Here's the git diff:

Code: Select all

diff --git a/include/rssfuncs.php b/include/rssfuncs.php
index 9a006f8..9d664ff 100644
--- a/include/rssfuncs.php
+++ b/include/rssfuncs.php
@@ -23,9 +23,8 @@
        function update_feedbrowser_cache() {
 
                $result = db_query("SELECT feed_url, site_url, title, COUNT(id) AS subscribers
-                       FROM ttrss_feeds WHERE (SELECT COUNT(id) = 0 FROM ttrss_feeds AS tf
-                               WHERE tf.feed_url = ttrss_feeds.feed_url
-                               AND (private IS true OR auth_login != '' OR auth_pass != '' OR feed_url LIKE '%:%@%/%'))
+                        FROM ttrss_feeds WHERE feed_url NOT IN (SELECT feed_url FROM ttrss_feeds
+                               WHERE private IS true OR auth_login != '' OR auth_pass != '' OR feed_url LIKE '%:%@%/%')
                                GROUP BY feed_url, site_url, title ORDER BY subscribers DESC LIMIT 1000");
 
                db_query("BEGIN");


May I have developer privileges on my gitlab account? The username's framasky.

[fox]

thanks but please use the normal contribution process via gitlab

e: you can also disable feedbrowser altogether via config.php, unless you need the functionality

[ldidry]

thanks but please use the normal contribution process via gitlab

Well, I followed the contribution instructions on https://tt-rss.org/gitlab/fox/tt-rss/wikis/HowToContribute:

1. Post your brilliant idea on the forums, ask to be given developer privileges. Preferably post diffs if you got them. Don't forget to mention your gitlab username.

So… what should I have done differently?

e: you can also disable feedbrowser altogether via config.php, unless you need the functionality

Good point. I wasn't aware of that. I don't know if the feedbrowser is used or not by users… so I will keep it.

[fox]

the only thing i need is your gitlab username to give you necessary permissions

e: which is right there in your first post, oh. done.

[holyship]

I also had some performance tuning recently, and I think there is a little optimize on the entries table:

check this: https://tt-rss.org/gitlab/fox/tt-rss/bl ... l.sql#L140

By default, MySQL will create a B tree index for the guid column, but it's better to be hash.