[Bug]: Unified Query Allows Unwieldy SQL Statement With External Storages

daveatpinellas commented 1 year ago

⚠️ This issue respects the following points: ⚠️

[X] This is a bug, not a question or a configuration/webserver/proxy issue.
[X] This issue is not already reported on Github (I've searched it).
[X] Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
[X] Nextcloud Server is running on 64bit capable CPU, PHP and OS.
[X] I agree to follow Nextcloud's Code of Conduct.

Bug description

If you have Nextcloud connected to multiple external storage devices, when you use the unified search feature it's generating a SQL statement with lots of OR statements and takes too long to complete. The CPU is pegged during this query and it continues even if you click focus out of the UI or navigate to another part of Nextcloud.

In some way probably there should be a throttle on allowing this many external storage connections in the SQL. Possibly it should break this into smaller pieces and run them in a loop to return the data.

Here is the statement that was generated, and this is taking in some cases 10 minutes to complete:

SELECT file.fileid, storage, path, path_hash, file.parent, file.name, mimetype, mimepart, size, mtime, storage_mtime, encrypted, etag, permissions, checksum, unencrypted_size FROM oc_filecache file LEFT JOIN oc_vcategory_to_object tagmap ON file.fileid = tagmap.objid LEFT JOIN oc_systemtag_object_mapping systemtagmap ON (file.fileid = systemtagmap.objectid) AND (systemtagmap.objecttype = 'files') LEFT JOIN oc_vcategory tag ON (tagmap.type = tag.type) AND (tagmap.categoryid = tag.id) AND (tag.type = 'files') AND (tag.uid = '#############') LEFT JOIN oc_systemtag systemtag ON (systemtag.id = systemtagmap.systemtagid) AND (systemtag.visibility = '1') WHERE ((tag.category COLLATE utf8mb4_general_ci LIKE '%smith%') OR (systemtag.name COLLATE utf8mb4_general_ci LIKE '%smith%')) AND (((storage = 12) AND ((path = 'files') OR (path LIKE 'files/%'))) OR (storage = 44) OR (storage = 47) OR (storage = 48) OR (storage = 50) OR (storage = 51) OR (storage = 54) OR (storage = 55) OR (storage = 62) OR (storage = 161) OR (storage = 205) OR (storage = 236) OR (storage = 236) OR (storage = 257) OR (storage = 294) OR (storage = 294) OR (storage = 341) OR (storage = 361)) ORDER BY mtime + '0' desc LIMIT 5

Steps to reproduce

Log into an account with lots of external storage connections
Click into the unified search box and enter a search
Using top, watch the huge load on the server

Expected behavior

In some way, the sql statement should have a level of reasonableness that prohibits this many external storage folders to be part of the query. Or send them one after the other in a loop.

Cc @icewind1991

XueSheng-GIT commented 1 year ago

Similar to https://github.com/nextcloud/server/issues/35776. For each shared file, another OR statement is required and search really gets slow. Maybe also realated: https://github.com/nextcloud/server/pull/37061

kesselb commented 1 year ago

Do you see a better execution time when you combine the OR (storage = x) query parts to OR (storage IN (x, y, z)) ?

XueSheng-GIT commented 1 year ago

@kesselb How could that be done? The search query is generated by nextcloud and includes (in my case) around thousand storages.

kesselb commented 1 year ago

@XueSheng-GIT you take one query from the slow query log, modify it manually and execute it ;) If we see a improvement we can think about a patch.

cra-taketomi-yo commented 1 year ago

The phenomenon that the CPU utilization rises when searching with the unified search feature has also been confirmed in version 25.0.6.

XueSheng-GIT commented 1 year ago

@kesselb maybe you want to have a second look at https://github.com/nextcloud/server/pull/37061 again. Search execution time did improve tremendously.

pjft commented 1 year ago

Can you test whether this helps?

https://github.com/nextcloud/server/issues/38243#issuecomment-1552714039

nextcloud / server