Optimization of queries on very large datasets about server

[csware]

Shouldn't that the DB optimizer do, to filter for fileid and storage first?

[kesselb]

Similar report: #28114

The SQL query belongs to: https://github.com/nextcloud/server/blob/master/lib/private/Preview/BackgroundCleanupJob.php

Basically would be to do some fast checks before running long and complex query. The second command of the terminal of the image, shows a basic command that it counts all the entries where the fileid is NULL and it returned zero.

There are no rows in oc_filecache where fileid is null because the fileid is the primary key and therefore your suggestion, to first select all rows with fileid is null, does not work.

[mateusfmcota]

There are no rows in oc_filecache where fileid is null because the fileid is the primary key and therefore your suggestion, to first select all rows with fileid is null, does not work.

About this, my idea was mainly because of this line

server/lib/private/Preview/BackgroundCleanupJob.php

Line 74 in 512753a

$qb->expr()->isNull('b.fileid')

, the b is from oc_filecache and checks if is null, so for me it should be nullable.

After a deeper investigation I found that the problem is with the left join, the database takes too much time(hours) to do it. I'm beginning think the problem could be on my mariadb database, it has around 5M of entries and a simple count it takes around 1 minute run.

So, this probably is a question for the foruns then,