File search is slow

ncp

#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

#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?