"Your Photos" generates long sql queries

HI

In the photo app “Your Photos” shows images chronologically.
Here I notice that scrolling is really slow and the log show long sql queries when requesting the next 25 images. (imageperrow * 5)

The long query looks like the following which takes the first time (before it gets cached) 4 to 5 seconds.

SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepart`, `size`, `mtime`, `storage_mtime`, `encrypted`, `etag`, `permissions`, `checksum`, `metadata_etag`, `creation_time`, `upload_time`

 FROM `oc_filecache` `file` LEFT JOIN `oc_filecache_extended` `fe` ON `file`.`fileid` = `fe`.`fileid`

 WHERE ((`mimetype` = 6) OR (`mimetype` = 11) OR (`mimetype` = 19) OR (`mimetype` = 13) OR (`mimetype` = 20)) AND (((`storage` = 3) AND ((`path` = 'files') OR (`path` LIKE 'files/%'))) OR ((`storage` = 5)

  AND ((`path` = 'files/Rezepte') OR (`path` LIKE 'files/Rezepte/%'))) OR ((`storage` = 5) AND ((`path` = 'files/photo')

  OR (`path` LIKE 'files/photo/%'))) OR ((`storage` = 4)

  AND (`path_hash` = ''))

  OR ((`storage` = 4) AND (`path_hash` = '')) OR ((`storage` = 4) AND (`path_hash` = ''))

  OR ((`storage` = 4) AND (`path_hash` = '')) OR ((`storage` = 4) AND (`path_hash` = ''))

  OR ((`storage` = 6) AND (`path_hash` = '')) OR ((`storage` = 6) AND (`path_hash` = ''))

  OR some more path_hashes....)

  ORDER BY `mtime` desc LIMIT 25 OFFSET 225;

I stripped it to generic items which should be possible to run on any nextcloud database not having the same hashes and files…
This request still consumes 4 seconds.
Maybe someone can try this on his side.

SELECT `file`.`fileid`, `storage`, `path`, `path_hash`, `file`.`parent`, `name`, `mimetype`, `mimepart`, `size`, `mtime`, `storage_mtime`, `encrypted`, `etag`, `permissions`, `checksum`, `metadata_etag`, `creation_time`, `upload_time`

 FROM `oc_filecache` `file` LEFT JOIN `oc_filecache_extended` `fe` ON `file`.`fileid` = `fe`.`fileid`

 WHERE ((`mimetype` = 6) OR (`mimetype` = 11) OR (`mimetype` = 19) OR (`mimetype` = 13) OR (`mimetype` = 20)) AND (((`storage` = 3) AND ((`path` = 'files') OR (`path` LIKE 'files/%'))) OR (`storage` = 5))

 ORDER BY `mtime` desc LIMIT 25 OFFSET 225;

My size of oc_filecache

MariaDB [nextcloud]> SELECT count(*) FROM oc_filecache;
+----------+
| count(*) |
+----------+
|   915601 |
+----------+
1 row in set (0.450 sec)

MariaDB [nextcloud]> SELECT count(*) FROM oc_filecache_extended;
+----------+
| count(*) |
+----------+
|   199811 |
+----------+
1 row in set (0.067 sec)

mysql/my.cnf

[server]

skip_name_resolve = 1

innodb_buffer_pool_size = 128M

innodb_buffer_pool_instances = 1

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 32M

innodb_max_dirty_pages_pct = 90

query_cache_type = 1

query_cache_limit = 2M

query_cache_min_res_unit = 2k

query_cache_size = 64M

tmp_table_size= 64M

max_heap_table_size= 64M

slow_query_log = 1

slow_query_log_file = /var/log/mysql/slow.log

long_query_time = 1

[client-server]

!includedir /etc/mysql/conf.d/

!includedir /etc/mysql/mariadb.conf.d/

[client]

default-character-set = utf8mb4

[mysqld]

character_set_server = utf8mb4

collation_server = utf8mb4_general_ci

transaction_isolation = READ-COMMITTED

binlog_format = ROW

innodb_large_prefix=on

innodb_file_format=barracuda

innodb_file_per_table=1

Showing big albums (not “Your Photos”) loads super fast no problem here.

So I wonder is it just on my machine showing this slow scrolling of Your Photos?
Can someone please try the stripped query on his side?
Thanks for any hint

regards
Jochen