report the total number of files:
SELECT COUNT(*) FROM mdl_files

report how many files the user has:
SELECT userid as REALUSERID, count(*) as numfiles
FROM mdl_files
WHERE userid != ''
GROUP BY userid
ORDER BY numfiles desc
LIMIT 1

timing (and explain plan) of (note you need to replace REALUSERID):
SELECT files1.contextid, files1.itemid, files1.filearea,
       files1.component, files1.filepath, files1.filename, files1.pathnamehash
  FROM mdl_files files1
  JOIN (
      SELECT contenthash, filename, MAX(id) AS id
        FROM mdl_files
       WHERE userid = REALUSERID
             AND filename != '.'
             AND ((filearea = 'draft' AND itemid = 0) OR filearea != 'draft')
             AND referencefileid IS NULL
      GROUP BY contenthash, filename
    ) files2 ON files1.id = files2.id
ORDER BY files1.timemodified DESC