Scaling of user statistics (#8277)

Fix https://github.com/FreshRSS/FreshRSS/issues/8268
To better support user management on FreshRSS instance with many users.

SQL speed improved. On a reduced test with 5 users, including some large accounts (PostgreSQL on a very tiny and slow server), improving from ~2.3s to ~1.8s, which gives ~20% speed improvement.

Then tested with 1000 users, with only the default feed (on my old desktop computer):

```sh
for i in {1..1000}; do ./cli/create-user.php --user=freshrss$i --password=freshrss; done
app/actualize_script.php
cli/access-permissions.sh
```

SQLite:

```console
$ time cli/user-info.php | wc -l
1001

real    0m1.366s
user    0m0.908s
sys     0m0.475s
```

PostgreSQL:

```console
$ time cli/user-info.php | wc -l
1001

real    0m28.498s
user    0m12.137s
sys     0m2.217s
```

MariaDB:

```console
# time ./cli/user-info.php | wc -l
1001

real    0m49.485s
user    0m1.276s
sys     0m2.258s
```

Yes, SQLite is much faster - not a surprise for such use-cases, where the TCP connection is not re-used.

I have added some CLI options to disable some statistics:

```sh
cli/user-info.php --no-db-size --no-db-counts
```

For the Web UI, I have disabled detailed user statistics if it takes too long, and retrieve missing user statistics asynchronously via JavaScript. Lazy loading of the user details based on IntersectionObserver, with maximum 10 requests in parallel.
Web UI tested on 1000 users as well. Checked with SeaMonkey.

10 files changed, 157 insertions, 105 deletions

diff --git a/app/Controllers/configureController.php b/app/Controllers/configureController.php
index 86943e663..8bc0d08c4 100644
--- a/app/Controllers/configureController.php
+++ b/app/Controllers/configureController.php
@@ -369,7 +369,7 @@ class FreshRSS_configure_Controller extends FreshRSS_ActionController {
 		$this->view->size_user = $databaseDAO->size();
 
 		if (FreshRSS_Auth::hasAccess('admin')) {
-			$this->view->size_total = $databaseDAO->size(true);
+			$this->view->size_total = $databaseDAO->size(all: true);
 		}
 
 		FreshRSS_View::prependTitle(_t('conf.archiving.title') . ' · ');
diff --git a/app/Controllers/userController.php b/app/Controllers/userController.php
index 84ef85335..6f5b27280 100644
--- a/app/Controllers/userController.php
+++ b/app/Controllers/userController.php
@@ -328,8 +328,14 @@ class FreshRSS_user_Controller extends FreshRSS_ActionController {
 		$this->view->show_email_field = FreshRSS_Context::systemConf()->force_email_validation;
 		$this->view->current_user = Minz_Request::paramString('u');
 
+		$fast = false;
+		$startTime = time();
 		foreach (self::listUsers() as $user) {
-			$this->view->users[$user] = $this->retrieveUserDetails($user);
+			if (!$fast && (time() - $startTime >= 3)) {
+				// Disable detailed user statistics if it takes too long, and will retrieve them asynchronously via JavaScript
+				$fast = true;
+			}
+			$this->view->users[$user] = $this->retrieveUserDetails($user, $fast);
 		}
 	}
 
@@ -806,11 +812,11 @@ class FreshRSS_user_Controller extends FreshRSS_ActionController {
 		FreshRSS_View::prependTitle($username . ' · ' . _t('gen.menu.user_management') . ' · ');
 	}
 
-	/** @return array{'feed_count':int,'article_count':int,'database_size':int,'language':string,'mail_login':string,'enabled':bool,'is_admin':bool,'last_user_activity':string,'is_default':bool} */
-	private function retrieveUserDetails(string $username): array {
-		$feedDAO = FreshRSS_Factory::createFeedDao($username);
-		$entryDAO = FreshRSS_Factory::createEntryDao($username);
-		$databaseDAO = FreshRSS_Factory::createDatabaseDAO($username);
+	/** @return array{feed_count:?int,article_count:?int,database_size:?int,language:string,mail_login:string,enabled:bool,is_admin:bool,last_user_activity:string,is_default:bool} */
+	private function retrieveUserDetails(string $username, bool $fast = false): array {
+		$feedDAO = $fast ? null : FreshRSS_Factory::createFeedDao($username);
+		$entryDAO = $fast ? null : FreshRSS_Factory::createEntryDao($username);
+		$databaseDAO = $fast ? null : FreshRSS_Factory::createDatabaseDAO($username);
 
 		$userConfiguration = FreshRSS_UserConfiguration::getForUser($username);
 		if ($userConfiguration === null) {
@@ -818,9 +824,9 @@ class FreshRSS_user_Controller extends FreshRSS_ActionController {
 		}
 
 		return [
-			'feed_count' => $feedDAO->count(),
-			'article_count' => $entryDAO->count(),
-			'database_size' => $databaseDAO->size(),
+			'feed_count' => isset($feedDAO) ? $feedDAO->count() : null,
+			'article_count' => isset($entryDAO) ? $entryDAO->count() : null,
+			'database_size' => isset($databaseDAO) ? $databaseDAO->size() : null,
 			'language' => $userConfiguration->language,
 			'mail_login' => $userConfiguration->mail_login,
 			'enabled' => $userConfiguration->enabled,
diff --git a/app/Models/DatabaseDAO.php b/app/Models/DatabaseDAO.php
index b34c0fc66..1a6a824e5 100644
--- a/app/Models/DatabaseDAO.php
+++ b/app/Models/DatabaseDAO.php
@@ -347,13 +347,8 @@ SQL;
 						//SQLite is the only one with database-level optimization, instead of at table level.
 						$this->optimize();
 					}
-				} else {
-					if ($databaseDAO->exits()) {
-						$nbEntries = $entryDAO->countUnreadRead();
-						if (isset($nbEntries['all']) && $nbEntries['all'] > 0) {
-							$error = 'Error: Destination database already contains some entries!';
-						}
-					}
+				} elseif ($databaseDAO->exits() && $entryDAO->count() > 0) {
+					$error = 'Error: Destination database already contains some entries!';
 				}
 				break;
 			default:
diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index b4f7451c7..eb800ff1e 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -1733,29 +1733,35 @@ SQL;
 		}
 	}
 
-	/** @return array<string,int> */
-	public function countUnreadRead(): array {
+	/** @return array{all:int,unread:int,read:int,favorites:int} */
+	public function countAsStates(?int $minPriority = null): array {
+		$values = [];
 		$sql = <<<'SQL'
-SELECT COUNT(e.id) AS count FROM `_entry` e
-	INNER JOIN `_feed` f ON e.id_feed=f.id
-	WHERE f.priority > 0
-UNION
-SELECT COUNT(e.id) AS count FROM `_entry` e
-	INNER JOIN `_feed` f ON e.id_feed=f.id
-	WHERE f.priority > 0 AND e.is_read=0
-SQL;
-		$res = $this->fetchColumn($sql, 0);
-		if ($res === null) {
-			return ['all' => -1, 'unread' => -1, 'read' => -1];
+			SELECT
+				COUNT(*) AS total,
+				COUNT(CASE WHEN e.is_read = 0 THEN 1 END) AS unread,
+				COUNT(CASE WHEN e.is_favorite = 1 THEN 1 END) AS favorites
+			FROM `_entry` e
+			SQL;
+		if ($minPriority !== null) {
+			$sql .= <<<'SQL'
+			INNER JOIN `_feed` f ON e.id_feed = f.id
+			WHERE f.priority > :priority
+			SQL;
+			$values[':priority'] = $minPriority;
 		}
-		rsort($res);
-		$all = (int)($res[0] ?? 0);
-		$unread = (int)($res[1] ?? 0);
-		return ['all' => $all, 'unread' => $unread, 'read' => $all - $unread];
+		$res = $this->fetchAssoc($sql, $values);
+		if ($res === null || !isset($res[0])) {
+			return ['all' => -1, 'unread' => -1, 'read' => -1, 'favorites' => -1];
+		}
+		$all = (int)($res[0]['total'] ?? 0);
+		$unread = (int)($res[0]['unread'] ?? 0);
+		$favorites = (int)($res[0]['favorites'] ?? 0);
+		return ['all' => $all, 'unread' => $unread, 'read' => $all - $unread, 'favorites' => $favorites];
 	}
 
 	public function count(?int $minPriority = null): int {
-		$sql = 'SELECT COUNT(e.id) AS count FROM `_entry` e';
+		$sql = 'SELECT COUNT(*) AS count FROM `_entry` e';
 		$values = [];
 		if ($minPriority !== null) {
 			$sql .= ' INNER JOIN `_feed` f ON e.id_feed=f.id';
@@ -1766,51 +1772,22 @@ SQL;
 		return isset($res[0]) ? (int)($res[0]) : -1;
 	}
 
-	public function countNotRead(?int $minPriority = null): int {
-		$sql = 'SELECT COUNT(e.id) AS count FROM `_entry` e';
-		if ($minPriority !== null) {
-			$sql .= ' INNER JOIN `_feed` f ON e.id_feed=f.id';
-		}
-		$sql .= ' WHERE e.is_read=0';
-		$values = [];
-		if ($minPriority !== null) {
-			$sql .= ' AND f.priority > :priority';
-			$values[':priority'] = $minPriority;
-		}
-		$res = $this->fetchColumn($sql, 0, $values);
-		return isset($res[0]) ? (int)($res[0]) : -1;
-	}
-
 	/** @return array{'all':int,'read':int,'unread':int} */
 	public function countUnreadReadFavorites(): array {
 		$sql = <<<'SQL'
-SELECT c FROM (
-	SELECT COUNT(e1.id) AS c, 1 AS o
-		FROM `_entry` AS e1
-		JOIN `_feed` AS f1 ON e1.id_feed = f1.id
-		WHERE e1.is_favorite = 1
-		AND f1.priority >= :priority1
-	UNION
-	SELECT COUNT(e2.id) AS c, 2 AS o
-		FROM `_entry` AS e2
-		JOIN `_feed` AS f2 ON e2.id_feed = f2.id
-		WHERE e2.is_favorite = 1
-		AND e2.is_read = 0 AND f2.priority >= :priority2
-	) u
-ORDER BY o
-SQL;
-		//Binding a value more than once is not standard and does not work with native prepared statements (e.g. MySQL) https://bugs.php.net/bug.php?id=40417
-		$res = $this->fetchColumn($sql, 0, [
-			':priority1' => FreshRSS_Feed::PRIORITY_CATEGORY,
-			':priority2' => FreshRSS_Feed::PRIORITY_CATEGORY,
-		]);
-		if ($res === null) {
+			SELECT
+				COUNT(*) AS total,
+				COUNT(CASE WHEN e.is_read = 0 THEN 1 END) AS unread
+			FROM `_entry` e
+			JOIN `_feed` f ON e.id_feed = f.id
+			WHERE e.is_favorite = 1 AND f.priority > :priority
+			SQL;
+		$res = $this->fetchAssoc($sql, [':priority' => FreshRSS_Feed::PRIORITY_HIDDEN]);
+		if ($res === null || !isset($res[0])) {
 			return ['all' => -1, 'unread' => -1, 'read' => -1];
 		}
-
-		rsort($res);
-		$all = (int)($res[0] ?? 0);
-		$unread = (int)($res[1] ?? 0);
+		$all = (int)($res[0]['total'] ?? 0);
+		$unread = (int)($res[0]['unread'] ?? 0);
 		return ['all' => $all, 'unread' => $unread, 'read' => $all - $unread];
 	}
 }
diff --git a/app/Models/View.php b/app/Models/View.php
index b7970b57e..d1d5d0d8e 100644
--- a/app/Models/View.php
+++ b/app/Models/View.php
@@ -44,12 +44,12 @@ class FreshRSS_View extends Minz_View {
 	public bool $signalError;
 
 	// Manage users
-	/** @var array{feed_count:int,article_count:int,database_size:int,language:string,mail_login:string,enabled:bool,is_admin:bool,last_user_activity:string,is_default:bool} */
+	/** @var array{feed_count:?int,article_count:?int,database_size:?int,language:string,mail_login:string,enabled:bool,is_admin:bool,last_user_activity:string,is_default:bool} */
 	public array $details;
 	public bool $disable_aside;
 	public bool $show_email_field;
 	public string $username;
-	/** @var array<array{language:string,enabled:bool,is_admin:bool,enabled:bool,article_count:int,database_size:int,last_user_activity:string,mail_login:string,feed_count:int,is_default:bool}> */
+	/** @var array<array{language:string,enabled:bool,is_admin:bool,enabled:bool,article_count:?int,database_size:?int,last_user_activity:string,mail_login:string,feed_count:?int,is_default:bool}> */
 	public array $users;
 
 	// Updates
diff --git a/app/views/user/details.phtml b/app/views/user/details.phtml
index 8bad08a81..9dd55d737 100644
--- a/app/views/user/details.phtml
+++ b/app/views/user/details.phtml
@@ -29,22 +29,22 @@
 
 		<div class="form-group">
 			<label class="group-name"><?= _t('admin.user.feed_count') ?></label>
-			<div class="group-controls">
-				<?= format_number($this->details['feed_count'] ?: 0) ?>
+			<div class="group-controls feed_count">
+				<?= is_numeric($this->details['feed_count']) ? format_number($this->details['feed_count']) : '?' ?>
 			</div>
 		</div>
 
 		<div class="form-group">
 			<label class="group-name"><?= _t('admin.user.article_count') ?></label>
-			<div class="group-controls">
-				<?= format_number($this->details['article_count'] ?: 0) ?>
+			<div class="group-controls article_count">
+				<?= is_numeric($this->details['article_count']) ? format_number($this->details['article_count']) : '?' ?>
 			</div>
 		</div>
 
 		<div class="form-group">
 			<label class="group-name"><?= _t('admin.user.database_size') ?></label>
-			<div class="group-controls">
-				<?= format_bytes($this->details['database_size']) ?>
+			<div class="group-controls database_size">
+				<?= is_numeric($this->details['database_size']) ? format_bytes($this->details['database_size']) : '?' ?>
 			</div>
 		</div>
 
diff --git a/app/views/user/manage.phtml b/app/views/user/manage.phtml
index db5b8c7dd..3f541c0f5 100644
--- a/app/views/user/manage.phtml
+++ b/app/views/user/manage.phtml
@@ -105,16 +105,17 @@
 			</thead>
 			<tbody>
 				<?php foreach ($this->users as $username => $values): ?>
-					<tr <?= $values['is_default'] ? 'class="default-user"' : '' ?>>
+					<tr <?= $values['is_default'] ? 'class="default-user"' : '' ?>
+						<?= is_numeric($values['feed_count']) ? '' : 'data-need-ajax="1"' ?>>
 						<td><a href="<?= _url('user', 'details', 'username', $username) ?>" class="configure open-slider" ><?= _i('configure') ?></a></td>
-						<td><?= $username ?></td>
+						<td class="username"><?= $username ?></td>
 						<td><?= $values['enabled'] ? '✔' : ' ' ?></td>
 						<td><?= $values['is_admin'] ? '✔' : ' ' ?></td>
 						<td><?= $values['mail_login'] ?></td>
 						<td><?= _t("gen.lang.{$values['language']}") ?></td>
-						<td><?= format_number($values['feed_count']) ?></td>
-						<td><?= format_number($values['article_count']) ?></td>
-						<td><?= format_bytes($values['database_size']) ?></td>
+						<td class="feed-count"><?= is_numeric($values['feed_count']) ? format_number($values['feed_count']) : '?' ?></td>
+						<td class="article-count"><?= is_numeric($values['article_count']) ? format_number($values['article_count']) : '?' ?></td>
+						<td class="database-size"><?= is_numeric($values['database_size']) ? format_bytes($values['database_size']) : '?' ?></td>
 						<td><?= $values['last_user_activity'] ?></td>
 					</tr>
 				<?php endforeach ?>
diff --git a/cli/README.md b/cli/README.md
index 47f28a7f7..b3ca09cb0 100644
--- a/cli/README.md
+++ b/cli/README.md
@@ -95,6 +95,8 @@ cd /usr/share/FreshRSS
 # -h, --human-readable display output in a human readable format
 # --header outputs some columns headers.
 # --json JSON format (disables --header and --human-readable but uses ISO Zulu format for dates).
+# --no-db-size for faster responses by disabling database size calculation.
+# --no-db-counts for faster responses by disabling counting the different types of articles in database.
 # --user indicates a username, and can be repeated.
 # Returns: 1) a * if the user is admin, 2) the name of the user,
 #  3) the date/time of last user action, 4) the size occupied,
diff --git a/cli/user-info.php b/cli/user-info.php
index 2771605ac..496d4bfdb 100755
--- a/cli/user-info.php
+++ b/cli/user-info.php
@@ -11,12 +11,18 @@ $cliOptions = new class extends CliOptionsParser {
 	public bool $header;
 	public bool $json;
 	public bool $humanReadable;
+	/** Disable database size */
+	public bool $noDbSize;
+	/** Disable database counts */
+	public bool $noDbCounts;
 
 	public function __construct() {
 		$this->addOption('user', (new CliOption('user'))->typeOfArrayOfString());
 		$this->addOption('header', (new CliOption('header'))->withValueNone());
 		$this->addOption('json', (new CliOption('json'))->withValueNone());
 		$this->addOption('humanReadable', (new CliOption('human-readable', 'h'))->withValueNone());
+		$this->addOption('noDbSize', (new CliOption('no-db-size'))->withValueNone());
+		$this->addOption('noDbCounts', (new CliOption('no-db-counts'))->withValueNone());
 		parent::__construct();
 	}
 };
@@ -58,15 +64,23 @@ if ($cliOptions->header) {
 foreach ($users as $username) {
 	$username = cliInitUser($username);
 
-	$catDAO = FreshRSS_Factory::createCategoryDao($username);
-	$feedDAO = FreshRSS_Factory::createFeedDao($username);
-	$entryDAO = FreshRSS_Factory::createEntryDao($username);
-	$tagDAO = FreshRSS_Factory::createTagDao($username);
-	$databaseDAO = FreshRSS_Factory::createDatabaseDAO($username);
-
-	$nbEntries = $entryDAO->countUnreadRead();
-	$nbFavorites = $entryDAO->countUnreadReadFavorites();
-	$feedList = $feedDAO->listFeedsIds();
+	if ($cliOptions->noDbCounts) {
+		$catDAO = null;
+		$feedDAO = null;
+		$tagDAO = null;
+		$nbEntries = null;
+	} else {
+		$catDAO = FreshRSS_Factory::createCategoryDao($username);
+		$feedDAO = FreshRSS_Factory::createFeedDao($username);
+		$entryDAO = FreshRSS_Factory::createEntryDao($username);
+		$tagDAO = FreshRSS_Factory::createTagDao($username);
+		$nbEntries = $entryDAO->countAsStates();
+	}
+	if ($cliOptions->noDbSize) {
+		$databaseDAO = null;
+	} else {
+		$databaseDAO = FreshRSS_Factory::createDatabaseDAO($username);
+	}
 
 	$data = [
 		'default' => $username === FreshRSS_Context::systemConf()->default_user ? '*' : '',
@@ -74,19 +88,21 @@ foreach ($users as $username) {
 		'admin' => FreshRSS_Context::userConf()->is_admin ? '*' : '',
 		'enabled' => FreshRSS_Context::userConf()->enabled ? '*' : '',
 		'last_user_activity' => FreshRSS_UserDAO::mtime($username),
-		'database_size' => $databaseDAO->size(),
-		'categories' => $catDAO->count(),
-		'feeds' => count($feedList),
-		'reads' => (int)$nbEntries['read'],
-		'unreads' => (int)$nbEntries['unread'],
-		'favourites' => (int)$nbFavorites['all'],
-		'tags' => $tagDAO->count(),
+		'database_size' => isset($databaseDAO) ? $databaseDAO->size() : '?',
+		'categories' => isset($catDAO) ? $catDAO->count() : '?',
+		'feeds' => isset($feedDAO) ? $feedDAO->count() : '?',
+		'reads' => isset($nbEntries) ? $nbEntries['read'] : '?',
+		'unreads' => isset($nbEntries) ? $nbEntries['unread'] : '?',
+		'favourites' => isset($nbEntries) ? $nbEntries['favorites'] : '?',
+		'tags' => isset($tagDAO) ? $tagDAO->count() : '?',
 		'lang' => FreshRSS_Context::userConf()->language,
 		'mail_login' => FreshRSS_Context::userConf()->mail_login,
 	];
 	if ($cliOptions->humanReadable) {	//Human format
 		$data['last_user_activity'] = date('c', $data['last_user_activity']);
-		$data['database_size'] = format_bytes($data['database_size']);
+		if (ctype_digit($data['database_size'])) {
+			$data['database_size'] = format_bytes($data['database_size']);
+		}
 	}
 
 	if ($cliOptions->json) {
diff --git a/p/scripts/extra.js b/p/scripts/extra.js
index dfeff9293..9eeefabfb 100644
--- a/p/scripts/extra.js
+++ b/p/scripts/extra.js
@@ -524,6 +524,60 @@ function init_details_attributes() {
 	});
 }
 
+function init_user_stats() {
+	const active = new Set();
+	const queue = [];
+	const limit = 10;	// Ensure not too many concurrent requests
+
+	const processQueue = () => {
+		while (queue.length > 0 && active.size < limit) {
+			const row = queue.shift();
+			const promise = (async () => {
+				row.removeAttribute('data-need-ajax');
+				try {
+					const username = row.querySelector('.username').textContent.trim();
+					const url = '?c=user&a=details&username=' + encodeURIComponent(username) + '&ajax=1';
+					const response = await fetch(url);
+					const html = await response.text();
+					const parser = new DOMParser();
+					const doc = parser.parseFromString(html, 'text/html');
+					row.querySelector('.feed-count').innerHTML = doc.querySelector('.feed_count').innerHTML;
+					row.querySelector('.article-count').innerHTML = doc.querySelector('.article_count').innerHTML;
+					row.querySelector('.database-size').innerHTML = doc.querySelector('.database_size').innerHTML;
+				} catch (err) {
+					console.error('Error fetching user stats', err);
+				}
+			})();
+
+			promise.finally(() => {
+				active.delete(promise);
+				processQueue();
+			});
+			active.add(promise);
+		}
+	};
+
+	// Retrieve user stats when the row becomes visible
+	const timers = new WeakMap();
+	const observer = new IntersectionObserver((entries) => {
+		entries.forEach(entry => {
+			if (entry.isIntersecting) {
+				const timer = setTimeout(() => {
+					// But wait a bit to avoid triggering on fast scrolls
+					observer.unobserve(entry.target);
+					queue.push(entry.target);
+					processQueue();
+				}, 100);
+				timers.set(entry.target, timer);
+			} else {
+				clearTimeout(timers.get(entry.target));
+			}
+		});
+	});
+
+	document.querySelectorAll('tr[data-need-ajax]').forEach(row => observer.observe(row));
+}
+
 function init_extra_afterDOM() {
 	if (!window.context) {
 		if (window.console) {
@@ -544,6 +598,7 @@ function init_extra_afterDOM() {
 		init_2stateButton();
 		init_update_feed();
 		init_details_attributes();
+		init_user_stats();
 
 		data_auto_leave_validation(document.body);