[Bug]: "/apps/recommendations/api/recommendations/always" is too slow

[anton-samal]

⚠️ 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 (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• Nextcloud Server is running on 64bit capable CPU, PHP and OS.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

I've noticed that when I open the main dashboard it takes 4-5 seconds to get the latest files. I got SQL query for this, and figured out that it takes 3,7 seconds. The longest operation is "Creating sort index".

The generated SQL:

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', 'unencrypted_size'
FROM 'oc_filecache' 'file'
LEFT JOIN 'oc_filecache_extended' 'fe' ON 'file'.'fileid' = 'fe'.'fileid'
WHERE (('mimetype' <> 2) OR ('size' = 0)) AND (('storage' = 2) AND (('path' = 'files') OR ('path' LIKE 'files/%')))
ORDER BY 'mtime' + '0' DESC
LIMIT 7

The problem is on ORDER BY 'mtime' + '0' DESC, without + '0' it works really fast (0,002 sec). It seems that with this zero the index is not taken into account

Steps to reproduce

1. Have a oc_filecache table with a size about 230 megabytes
2. Open dashboard page
3. Check how long a request to /index.php/apps/recommendations/api/recommendations/always is running

Expected behavior

query should not run for 4 seconds

Installation method

Community Docker image

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.0

Web server

Apache (supported)

Database engine version

MariaDB

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

No response

Are you using the Nextcloud Server Encryption module?

No response

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": [
            "192.168.0.104"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "mysql",
        "version": "24.0.5.1",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "3306",
        "dbtableprefix": "oc_",
        "mysql.utf8mb4": true,
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "maintenance": false,
        "theme": "",
        "loglevel": 2,
        "updater.release.channel": "stable",
        "app_install_overwrite": [
            "spreed",
            "calendar"
        ],
        "mail_smtpmode": "smtp",
        "mail_sendmailmode": "smtp",
        "mail_smtpport": "465",
        "mail_smtpauthtype": "PLAIN",
        "memcache.local": "\\OC\\Memcache\\APCu",
        "default_phone_region": "BY",
        "trusted_proxies": "***REMOVED SENSITIVE VALUE***"
    }
}

List of activated Apps

Enabled:
  - accessibility: 1.10.0
  - activity: 2.16.0
  - audioplayer: 3.3.1
  - calendar: 3.5.1
  - circles: 24.0.1
  - cloud_federation_api: 1.7.0
  - comments: 1.14.0
  - contacts: 4.2.2
  - contactsinteraction: 1.5.0
  - dashboard: 7.4.0
  - dav: 1.22.0
  - federatedfilesharing: 1.14.0
  - federation: 1.14.0
  - files: 1.19.0
  - files_external: 1.16.1
  - files_pdfviewer: 2.5.0
  - files_rightclick: 1.3.0
  - files_sharing: 1.16.2
  - files_trashbin: 1.14.0
  - files_versions: 1.17.0
  - files_videoplayer: 1.13.0
  - firstrunwizard: 2.13.0
  - logreader: 2.9.0
  - lookup_server_connector: 1.12.0
  - mail: 1.14.1
  - metadata: 0.16.0
  - nextcloud_announcements: 1.13.0
  - notifications: 2.12.1
  - oauth2: 1.12.0
  - password_policy: 1.14.0
  - photos: 1.6.0
  - privacy: 1.8.0
  - provisioning_api: 1.14.0
  - recommendations: 1.3.0
  - serverinfo: 1.14.0
  - settings: 1.6.0
  - sharebymail: 1.14.0
  - spreed: 14.0.5
  - support: 1.7.0
  - survey_client: 1.12.0
  - systemtags: 1.14.0
  - text: 3.5.1
  - theming: 1.15.0
  - twofactor_backupcodes: 1.13.0
  - updatenotification: 1.14.0
  - user_status: 1.4.0
  - viewer: 1.8.0
  - weather_status: 1.4.0
  - workflowengine: 2.6.0
Disabled:
  - admin_audit
  - encryption
  - user_ldap

Nextcloud Signing status

No errors have been found.

Nextcloud Logs

No response

Additional info

No response

[joshtrichards]

This query is coming from Server via getRecent().

Improved in nextcloud/server#33788