FYI, I did some playing around with queries, first, might I suggest something like this:
SELECT File.ID as FileID, FilesetEntry.Lastmodified, FileBlockset.Length,
MetaBlockset.FullHash AS Metahash, MetaBlockset.Length as Metasize
FROM FilesetEntry
INNER JOIN Fileset ON (FileSet.ID = FilesetEntry.FilesetID)
INNER JOIN File ON (File.ID = FilesetEntry.FileID)
INNER JOIN Metadataset ON (Metadataset.ID = File.MetadataID)
INNER JOIN Blockset AS MetaBlockset ON (MetaBlockset.ID = Metadataset.BlocksetID)
LEFT JOIN Blockset AS FileBlockset ON (FileBlockset.ID = File.BlocksetID)
WHERE FilesetID = ? AND File.Path = ?
LIMIT 1
From an SQL perspective, this query is a little nicer. First, prefer JOIN to using WHERE, as this allows both variance in HOW the join occurs, and short-circuiting the joining process (ie. if a JOIN can’t happen, it won’t even attempt to look for data from the other tables).
Second, the more important joins (the ones more likely to eliminate cut down the number of resulting rows) are listed earlier.
Third, The quicker WHERE condition should go first (presumably a numerical lookup on a smaller set of data is faster than a text-based lookup, though both are indexed).
Finally, and this is the MOST important, ADD THE LIMIT 1 BACK!
In my own testing, that LIMIT 1 changes the fetch time from say 10-15ms to >350ms. Because even though your initial query is guaranteed to return exactly ONE result, the SQL server will KEEP LOOKING for any other matches, unless there is a LIMIT 1. Add back the LIMIT 1 will increase the SQL performance of your query DRASTICALLY.
I’m NOT an SQL expert, but I have used it enough to get some sense of optimizing it. But as I said, the most important thing for the query is the LIMIT 1, from my own experiments, that is easily the root cause of this query taking 40x longer with the ‘new’ version.