File search is slow

ℹ️ Support 📦 Appliances (Docker, Snappy, VM, NCP, AIO)
1

Hi,
I’m trying Nextcloud (using nextcloudplus dist) to replace Google Drive. For now I like it but the search in WebUI and Android App is really slow. I’ve made some tests with the SQL Nextcloud uses to find files (see below). With 750 000 files a search take more than 1mn, if I simplify the request by removing the ‘storage=3’ it is 2sec! Do you know why I have this behaviour? Is there some way to improve performances?

mysql -u root nextcloud -N -e "SELECT count(*) FROM oc_filecache WHERE storage=3"
+--------+
| 739364 |
+--------+
time mysql -u root nextcloud -N -e "SELECT * FROM oc_filecache WHERE storage=3 AND name LIKE '%Notes%Nextcloud%';"
+---------+---+------------------------------------------------------+----------------------------------+---------+---------------------+---+---+-------+------------+------------+---+---+----------------------------------+------+------+
| 1824668 | 3 | Informatique_new/Cloud/Nextcloud/Notes Nextcloud.txt | 5cc21fa5f4141cc318f79dee75678fb8 | 1824661 | Notes Nextcloud.txt | 8 | 7 | 46958 | 1533635763 | 1533635763 | 0 | 0 | 72bcf619f9d34f73331c4e79e6c8563e |   27 |      |
+---------+---+------------------------------------------------------+----------------------------------+---------+---------------------+---+---+-------+------------+------------+---+---+----------------------------------+------+------+

real	1m3,241s
user	0m0,000s
sys	0m0,010s
time mysql -u root nextcloud -N -e "SELECT * FROM oc_filecache WHERE name LIKE '%Notes%Nextcloud%';"
+---------+---+------------------------------------------------------+----------------------------------+---------+---------------------+---+---+-------+------------+------------+---+---+----------------------------------+------+------+
| 1824668 | 3 | Informatique_new/Cloud/Nextcloud/Notes Nextcloud.txt | 5cc21fa5f4141cc318f79dee75678fb8 | 1824661 | Notes Nextcloud.txt | 8 | 7 | 46958 | 1533635763 | 1533635763 | 0 | 0 | 72bcf619f9d34f73331c4e79e6c8563e |   27 |      |
+---------+---+------------------------------------------------------+----------------------------------+---------+---------------------+---+---+-------+------------+------------+---+---+----------------------------------+------+------+

real	0m2,411s
user	0m0,004s
sys	0m0,007s
1 Like
2

Have you thought about using the fulltextsearch app for that? It should significantly reduce the time spent for searching.

3

Yes I have thought about it reading some post but all I want for now is fast file name search only. It’s enough for me for years (use locate or everything on Win). Will fulltextsearch will improve this?

4

Yes, it will.

5

Hi! I have a related issue that I cannot get my head around (Nextcloud v13):

When I search in the Web interface it takes about 20 seconds to find results. When I search directly via SQL it is less than a second:
``SELECT * FROM oc_filecache WHERE name REGEXP ‘.foobar.’ LIMIT 50(0.804 s)

Any idea where it could hang that long?

6

The same thing happens on my nextcloud server. I’ve noticed that after the search there’s a lot of 404 responses for the preview.png.

So maybe after the database result comes in for the back-end, the server has to generate all those previews and that indeed would take a long time.

failed_previews
failed_previews1178×342 28.5 KB

7

I’m having the same issue here. The search is basically non-functional. I have ~750,000 entries in the oc_filecache table, but a direct SQL query is fast, so not sure why search results are so slow.

As an aside, I have installed elastic search and that is indexing at the moment, but it sounds like that will take days to complete and I’m not really sure that it will help, and even if it does, doesn’t become confusing for users to have two different search interfaces - the main one, as well as the fulltextsearch interface?

8

2 posts were split to a new topic: Search is slow since NC 20

9

you might be running into this issue: Files search takes a long time with unified search · Issue #23835 · nextcloud/server · GitHub