[Bug]: Extremely slow query when lots of files indexed in filecache

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration/webserver/proxy issue.
• This issue is not already reported on Github OR Nextcloud Community Forum (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

Recently my mysql instance was running 100% load so i've investigated and it came down to this query:

| 648 | nextcloud       | localhost | nextcloud | Query   |     0 | executing              | SELECT `fileid`, `name`
					FROM `oc_filecache`
					JOIN `oc_mimetypes` ON `oc_mimetypes`.`id` = `oc_filecache`.`mimetype`
					WHERE `parent` = 737310 AND `oc_mimetypes`.`mimetype` LIKE 'image%' |

explain did show sub optimal index usage:

mysql> explain SELECT `fileid`, `name`
    ->           FROM `oc_filecache`
    ->           JOIN `oc_mimetypes` ON `oc_mimetypes`.`id` = `oc_filecache`.`mimetype`
    ->           WHERE `parent` = 737310 AND `oc_mimetypes`.`mimetype` LIKE 'image%';
+----+-------------+--------------+------------+--------+-------------------------------+-----------+---------+---------------------------------+-------+----------+-------------+
| id | select_type | table        | partitions | type   | possible_keys                 | key       | key_len | ref                             | rows  | filtered | Extra       |
+----+-------------+--------------+------------+--------+-------------------------------+-----------+---------+---------------------------------+-------+----------+-------------+
|  1 | SIMPLE      | oc_filecache | NULL       | ref    | fs_parent_name_hash,fs_parent | fs_parent | 8       | const                           | 21550 |   100.00 | NULL        |
|  1 | SIMPLE      | oc_mimetypes | NULL       | eq_ref | PRIMARY,mimetype_id_index     | PRIMARY   | 8       | nextcloud.oc_filecache.mimetype |     1 |    12.84 | Using where |
+----+-------------+--------------+------------+--------+-------------------------------+-----------+---------+---------------------------------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

I've fixed it by adding this index:

mysql> alter table `oc_filecache` add index idx_ext_filecache_mimetype(`parent`,`mimetype`);

Now the explain looks more sane (as in using the proper index that optimizes the query away):

mysql> explain SELECT `fileid`, `name`           FROM `oc_filecache`           JOIN `oc_mimetypes` ON `oc_mimetypes`.`id` = `oc_filecache`.`mimetype`           WHERE `parent` = 737310 AND `oc_mimetypes`.`mimetype` LIKE 'image%';
+----+-------------+--------------+------------+-------+----------------------------------------------------------+----------------------------+---------+---------------------------------+------+----------+--------------------------+
| id | select_type | table        | partitions | type  | possible_keys                                            | key                        | key_len | ref                             | rows | filtered | Extra                    |
+----+-------------+--------------+------------+-------+----------------------------------------------------------+----------------------------+---------+---------------------------------+------+----------+--------------------------+
|  1 | SIMPLE      | oc_mimetypes | NULL       | range | PRIMARY,mimetype_id_index                                | mimetype_id_index          | 1022    | NULL                            |   14 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | oc_filecache | NULL       | ref   | fs_parent_name_hash,fs_parent,idx_ext_filecache_mimetype | idx_ext_filecache_mimetype | 16      | const,nextcloud.oc_mimetypes.id |    2 |   100.00 | NULL                     |
+----+-------------+--------------+------------+-------+----------------------------------------------------------+----------------------------+---------+---------------------------------+------+----------+--------------------------+

and my mysql load is back to almost 0.

Hope this helps.

Steps to reproduce

mysql> select count(*) from oc_filecache;
+----------+
| count(*) |
+----------+
|  2661320 |
+----------+

Expected behavior

DB load not 100%

Nextcloud Server version

31

Operating system

Other

PHP engine version

PHP 8.3

Web server

Nginx

Database engine version

MySQL

Is this bug present after an update or on a fresh install?

None

Are you using the Nextcloud Server Encryption module?

Encryption is Disabled

What user-backends are you using?

• Default user-backend (database)
• LDAP/ Active Directory
• SSO - SAML
• Other

Configuration report

{
    "system": {
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "***REMOVED SENSITIVE VALUE***"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "mysql",
        "version": "31.0.6.2",
        "overwrite.cli.url": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "maintenance": false,
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "mysql.utf8mb4": true,
        "memcache.local": "\\OC\\Memcache\\Memcached",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 6379,
            "timeout": 0,
            "password": "***REMOVED SENSITIVE VALUE***"
        },
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpmode": "sendmail",
        "mail_sendmailmode": "smtp",
        "forbidden_filename_characters": [
            "\\",
            "\/"
        ],
        "forbidden_filename_extensions": [
            ".filepart",
            ".part"
        ],
        "defaultapp": "",
        "maintenance_window_start": 1,
        "localstorage.allowsymlinks": true,
        "default_phone_region": "***REMOVED SENSITIVE VALUE***",
        "theme": "",
        "loglevel": 2
    }
}

List of activated Apps

Enabled:
  - activity: 4.0.0
  - admin_audit: 1.21.0
  - announcementcenter: 7.1.3
  - app_api: 5.0.2
  - bruteforcesettings: 4.0.0
  - calendar: 5.3.5
  - camerarawpreviews: 0.8.7
  - circles: 31.0.0
  - cloud_federation_api: 1.14.0
  - comments: 1.21.0
  - contacts: 7.1.5
  - contactsinteraction: 1.12.0
  - dashboard: 7.11.0
  - dav: 1.33.0
  - deck: 1.15.1
  - federatedfilesharing: 1.21.0
  - federation: 1.21.0
  - files: 2.3.1
  - files_accesscontrol: 2.0.0
  - files_automatedtagging: 2.0.0
  - files_confidential: 3.3.0
  - files_downloadlimit: 4.0.0
  - files_external: 1.23.0
  - files_pdfviewer: 4.0.0
  - files_reminders: 1.4.0
  - files_retention: 2.0.1
  - files_sharing: 1.23.1
  - files_trashbin: 1.21.0
  - files_versions: 1.24.0
  - firstrunwizard: 4.0.0
  - forms: 5.1.2
  - groupfolders: 19.1.2
  - htmlviewer: 31.0.0
  - imageconverter: 2.0.5
  - integration_giphy: 2.1.0
  - logreader: 4.0.0
  - lookup_server_connector: 1.19.0
  - mail: 5.1.7
  - metadata: 0.22.0
  - music: 2.2.0
  - news: 26.0.2
  - nextcloud_announcements: 3.0.0
  - notes: 4.12.2
  - notifications: 4.0.0
  - oauth2: 1.19.1
  - password_policy: 3.0.0
  - passwords: 2025.7.20
  - photos: 4.0.0-dev.1
  - privacy: 3.0.0
  - profile: 1.0.0
  - provisioning_api: 1.21.0
  - recognize: 9.0.3
  - recommendations: 4.0.0
  - related_resources: 2.0.0
  - richdocuments: 8.7.2
  - serverinfo: 3.0.0
  - settings: 1.14.0
  - sharebymail: 1.21.0
  - sociallogin: 6.0.2
  - socialsharing_email: 3.3.0
  - socialsharing_facebook: 3.3.0
  - socialsharing_twitter: 3.3.0
  - spreed: 21.1.1
  - survey_client: 3.0.0
  - systemtags: 1.21.1
  - tables: 0.9.4
  - text: 5.0.0
  - theming: 2.6.1
  - twofactor_backupcodes: 1.20.0
  - twofactor_nextcloud_notification: 5.0.0
  - twofactor_totp: 13.0.0-dev.0
  - updatenotification: 1.21.0
  - user_status: 1.11.0
  - viewer: 4.0.0
  - weather_status: 1.11.0
  - webhook_listeners: 1.2.0
  - workflowengine: 2.13.0
Disabled:
  - encryption: 2.19.0
  - support: 3.0.0 (installed 2.0.0)
  - suspicious_login: 9.0.1 (installed 8.0.0)
  - user_ldap: 1.22.0

Nextcloud Signing status

No errors have been found.

Nextcloud Logs

Additional info

No response

Start by tracing the filecache/mimetypes query and the existing database schema or migration entry points; the report does not identify source files or tests. Evaluate the proposed composite index across supported database backends, then confirm that the query plan and load improve for large filecache tables.