Use MySQL/MariaDB fulltext indexing on oc_filecache to speed up search results

Steps to reproduce

1. Run Nextcloud on a modern multi-core 64 bit machine with more than a few 100 000 files.
2. Try and search for a file using the normal search function (no apps/add-ons).

Expected behaviour

Nextcloud should return a set of results in a timely manner (less than 10 seconds ideally).

Actual behaviour

Nextcloud never returns the results.
Underneath, the MySQL/MariaDB server utilizes 100% on one of the server's CPU cores for some time (more than a minute).

Server configuration

Ubuntu 18.04 running Nextcloud 16 Docker Hub image.
MariaDB 10.3.12 (Docker Hub) running off a RAM drive.

Cause

In lib/private/Files/Cache/Cache.php, line 646:

// Original
$sql = ’
SELECT fileid, storage, path, parent, name,
mimetype, storage_mtime, mimepart, size, mtime,
encrypted, etag, permissions, checksum
FROM *PREFIX*filecache
WHERE storage = ? AND name ILIKE ?’;

This query doesn't use an index AFAIK, it scans the entire table, which is why it is slow.

Adding a fulltext index and modifying the query as below significantly reduces the query time and makes the search function usable :

// MariaDB/MySQL FULLTEXT Index
$sql = ’
SELECT fileid, storage, path, parent, name,
mimetype, storage_mtime, mimepart, size, mtime,
encrypted, etag, permissions, checksum
FROM *PREFIX*filecache
WHERE storage = ? AND MATCH(name) AGAINST (?)’;

I don't fully understand how Nextcloud's database schema migration works (the above requires a fulltext index). The fulltext index can be added like so :
ALTER TABLE oc_filecache ADD FULLTEXT INDEX name_idx (name);

Read lib/private/Files/Cache/Cache.php around line 646 and investigate Nextcloud’s database schema migration process for the required MySQL/MariaDB full-text index. Done means the file search uses the proposed indexed lookup where supported, the index is created through the migration system, and searches return results without the reported prolonged database scan.