[Bug]: preview:generate-all triggers DELETE on oc_filecache by path_hash causing full table scan on MariaDB

[chtnet81]

⚠️ 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

When running occ preview:generate-all on Nextcloud 33.0.0 with MariaDB, preview generation was extremely slow.

While investigating this, I found that Nextcloud repeatedly executes statements like:

DELETE FROM oc_filecache WHERE path_hash = ''

On my system, this query caused a full table scan on oc_filecache.

Table sizes on this system at the time of testing:

• oc_filecache: about 1,266,743 rows
• oc_previews: about 47,282 rows

SHOW TABLE STATUS reported:

• oc_filecache: Data_length = 360,677,376, Index_length = 739,917,824
• oc_previews: Data_length = 6,832,128, Index_length = 6,324,224

Environment:

• Nextcloud: 33.0.0
• Database: MariaDB
• Preview generation command:
  sudo -u www-data php /var/www/nextcloud/occ preview:generate-all -vv

Observed behavior:

• Preview generation was extremely slow, sometimes taking tens of seconds per image.
• EXPLAIN DELETE FROM oc_filecache WHERE path_hash = '...' showed:
  • type = ALL
  • key = NULL
  • rows ≈ 1.26M
• SHOW CREATE TABLE oc_filecache showed the standard composite index:
  • UNIQUE KEY fs_storage_path_hash (storage, path_hash)
• Because the query filters only by path_hash, MariaDB did not use the composite index.

Temporary workaround:
I manually added this index:

ALTER TABLE oc_filecache
ADD INDEX fs_path_hash_only (path_hash),
ALGORITHM=INPLACE,
LOCK=NONE;

After that:

• EXPLAIN DELETE FROM oc_filecache WHERE path_hash = '...' changed to:
  • type = range
  • key = fs_path_hash_only
  • rows = 1
• Preview generation became significantly faster immediately.

This suggests that the current query pattern in the preview/filecache code path can trigger very expensive full table scans on MariaDB.

Steps to reproduce

1. Use Nextcloud 33.0.0 with MariaDB and a larger oc_filecache table (~1.26 million rows in my case)
2. Install and use the Preview Generator app
3. Run:
   sudo -u www-data php /var/www/nextcloud/occ preview:generate-all -vv
4. Observe that preview generation is very slow
5. While the command is running, inspect MariaDB and check queries related to oc_filecache
6. Run:
   EXPLAIN DELETE FROM oc_filecache WHERE path_hash = '<some hash>'
7. Observe that the query plan shows a full table scan (type = ALL, key = NULL)

Expected behavior

• preview generation should not trigger full table scans on oc_filecache
• either the query should use existing indexes better, or the schema/index strategy should be adjusted

Nextcloud Server version

33

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.3

Web server

Apache (supported)

Database engine version

MariaDB

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

Upgraded to a MAJOR version (ex. 31 to 32)

Are you using the Nextcloud Server Encryption module?

None

What user-backends are you using?

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

Configuration report

List of activated Apps

Nextcloud Signing status

Nextcloud Logs

Additional info

No response

[SystemKeeper]

I think this is done in #58431 already