Faster recent tab for old slow servers

💻 Development
1

On my old server, Recent file tab was very slow, it used to take 30 seconds to start, here is how to reduce its loading time to about 3 secs

The main idea would be to put recent files to another table, because sorting big table like the main file table can take a long time.
You will need full access to your to both nextcloud files and your SQL database
Be aware that done like this, every data in your current recent tab will be wiped (not the files themselves but they won‘t be in recent tab anymore until you modify them) expect if you copy latest 100 entries yourself.
First, create a new table in NextCloud database, called oc_filerecent with to columns : id (integer primary key autoincrement) and fileid (foreign constraint on fileid of oc_filecache)

For each SQL command, be aware to modify “oc_” with your prefix in case you’ve got a different one.

CREATE TABLE `oc_filerecent` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `fileid` int(11) NOT NULL
);

add constraint

ALTER TABLE `oc_filerecent`
  ADD CONSTRAINT `deleteforeign` FOREIGN KEY (`fileid`) REFERENCES `oc_filecache` (`fileid`) ON DELETE CASCADE ON UPDATE CASCADE;

Then, we need to create a new trigger on update on filecache:

DELIMITER &&
DROP TRIGGER IF EXISTS `file_recent_update_trigger`&&
CREATE DEFINER=`root`@`localhost` TRIGGER `file_recent_update_trigger` AFTER UPDATE ON `oc_filecache` 
FOR EACH ROW 
BEGIN
 if NEW.mtime > OLD.mtime AND NEW.mimetype <> 2 AND NEW.path NOT  LIKE 'appdata_%' THEN
 	DELETE FROM oc_filerecent where fileid = NEW.fileid;
	INSERT INTO oc_filerecent VALUES(0,NEW.fileid);
    DELETE FROM oc_filerecent
	WHERE id NOT IN ( 
	  SELECT id
	  FROM (
	    SELECT id
	    FROM oc_filerecent
	    ORDER BY id DESC
	    LIMIT 100
	  ) foo
	);
    
END IF;
END&&
DELIMITER ;

This can also be triggered when creating a new file, just create the same trigger with INSERT instead of UPDATE

Then, we will have to modify NextCloud php code, hoping that updates to come won‘t break it:

edit lib/private/Files/Node/Folder.php from your NextCloud folder and look for the getRecent method

Find this specific string :

$query = $builder
                        ->select('f.*')
                        ->from('filecache', 'f')
                        ->andWhere($builder->expr()->in('f.storage', $builder->createNamedParameter($storageIds, IqueryBuilder::PARAM_INT_ARRAY)))

then add:

                    ->andWhere('f.`fileid` in (SELECT fileid from *PREFIX*filerecent)')

Resulting in this:

            $query = $builder
                    ->select('f.*')
                    ->from('filecache', 'f')
                    ->andWhere($builder->expr()->in('f.storage', $builder->createNamedParameter($storageIds, IQueryBuilder::PARAM_INT_ARRAY)))
                    ->andWhere('f.`fileid` in (SELECT fileid from *PREFIX*filerecent)')
                    ->andWhere($builder->expr()->orX(
                    // handle non empty folders separate
                            $builder->expr()->neq('f.mimetype', $builder->createNamedParameter($folderMimetype, IQueryBuilder::PARAM_INT)),
                            $builder->expr()->eq('f.size', new Literal(0))
                    ))
                    ->orderBy('f.mtime', 'DESC')
                    ->setMaxResults($limit)
                    ->setFirstResult($offset);

Done ! Enjoy