uc_file should try to validate a file using something other than file_key first

Proof of concept, the results were immediate.

In uc_file.module (anywhere, its just a hack of uc_file_get_by_hash()):

/**
 * Retrieve a file by uid and fid, if available.
 *
 * @param $fid
 *   Fid of the file being downloaded
 * @param $uid
 *   User's uid from $global
 * @return:
 *   A uc_file object.
 */
function &uc_file_get_by_fid_uid($fid, $uid) {
  $cache = _uc_file_get_cache();

  if (!isset($cache[$key])) {
    
    $cache[$key] = db_fetch_object(db_query("SELECT * FROM {uc_file_users} ufu ".
      "LEFT JOIN {uc_files} uf ON uf.fid = ufu.fid ".
      "WHERE ufu.fid = %d and ufu.uid = %d", $fid, $uid));
    $cache[$key]->addresses = unserialize($cache[$key]->addresses);
  }

  return $cache[$key];
}

In uc_file.pages.inc (line ~136):

  if ($user->uid > 0) {
    $file_download = uc_file_get_by_fid_uid($fid, $user->uid);
  }
  else {
    $file_download = uc_file_get_by_key($key);
  }

If you think this is a good idea, I'll write a patch. But like I said, works for me and takes the load off the server without having a compound index on fid and file_key. That might be another good thing to do:

create index uc_file_fid_file_key ON uc_file_users(fid, file_key);

Thoughts?

Updating this, just ran into the issue again with someone using a download accelerator. Brought the site to a screeching halt because he was downloading 4 files simultaneously, using about 5 connections each. This caused 20 threads to open up in mysql, each of them doing a text search for the hash.

I'll be building the compound index tomorrow morning, and investigating other ways to work around this. The hash is good to have but I think we should look at using alternatives first, and only use the hash as a last resort.

Yeah, that's part of the solution, I think. I'm interested in running some benchmarks before and after to see what kind of an improvement I get.

It's a shame that file_key wasn't already an index and we in particular had to wait until we already had 2+ million rows in the table :)

I was able to index it, and frankly, I think using the integer is still faster than a keyed index. Especially in our case where we literally have 2.5 million rows to search through; though I could be wrong.

I think indexing it is still a good thing to do, or possibly even a compound index on uid and file_key which is how the query runs.