The project/+download page issues at least 2 queries for every file listed

Below is a query, provided by Stuart, which retrieves everything needed to render that page.

The following query retrieves all the information I think is needed to render the entire report. It runs in about a second for bzr, and gives the running hit count rather than the cached count (there is no performance improvement using LibraryFileAlias.hits over SUM(count) ). It should be possible to convert this to Storm syntax if you want. The result will need to be transformed into a data structure usable by the existing template, or the existing template rewritten.

SELECT
    Product.name,
    ProductSeries.name,
    SignatureAlias.id AS signature_id,
    Milestone.name,
    ProductReleaseFile.description,
    LibraryFileAlias.id,
    LibraryFileAlias.filename,
    LibraryFileContent.filesize,
    SUM(count),
    MAX(day) AS last_downloaded
FROM
    Product
    JOIN ProductSeries ON ProductSeries.product = Product.id
    JOIN Milestone ON Milestone.productseries = ProductSeries.id
    JOIN ProductRelease ON ProductRelease.milestone = Milestone.id
    JOIN ProductReleaseFile
        ON ProductReleaseFile.productrelease = ProductRelease.id
    JOIN LibraryFileAlias
        ON LibraryFileAlias.id = ProductReleaseFile.libraryfile
    JOIN LibraryFileContent
        ON LibraryFileContent.id = LibraryFileAlias.content
    LEFT OUTER JOIN LibraryFileDownloadCount
        ON LibraryFileDownloadCount.libraryfilealias = LibraryFileAlias.id
    LEFT OUTER JOIN LibraryFileAlias AS SignatureAlias
        ON SignatureAlias.id = ProductReleaseFile.signature
WHERE
    Product.name = 'bzr'

GROUP BY
    Product.name,
    ProductSeries.name,
    signature_id,
    Milestone.name,
    ProductReleaseFile.description,
    LibraryFileAlias.id,
    LibraryFileAlias.filename,
    LibraryFileContent.filesize

ORDER BY
    ProductSeries.name,
    Milestone.name,
    LibraryFileAlias.filename;