Replace `filesize > 0` with `filename <> '.'`

[eduardokraus]

While analyzing the performance of a query used to identify files that have not yet been synchronized with ObjectFS, I identified a significant performance improvement by replacing the filter f.filesize > 0 with f.filename <> '.'.

Technical context

In Moodle, records in mdl_files with filename = '.' represent virtual directories, not real files. These records do not have physical content. The filesize > 0 filter, on the other hand, is less selective (most real files have filesize > 0) and results in a broader range scan, which tends to generate more index/table reads than necessary.

In

moodle-tool_objectfs/classes/local/object_manipulator/candidates/checker_candidates.php

Line 45 in 0af0997

WHERE f.filesize > 0

change from:

public function get_candidates_sql() {
    return 'SELECT f.contenthash
              FROM {files} f
         LEFT JOIN {tool_objectfs_objects} o ON f.contenthash = o.contenthash
             WHERE f.filesize > 0
               AND o.location is NULL
          GROUP BY f.contenthash';
}

to:

public function get_candidates_sql() {
    return 'SELECT f.contenthash
              FROM {files} f
         LEFT JOIN {tool_objectfs_objects} o ON f.contenthash = o.contenthash
             WHERE f.filename <> '.'
               AND o.location is NULL
          GROUP BY f.contenthash';
}

With around 2 million rows, the execution time was reduced by half.