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

Bug #389596 reported by Guilherme Salgado on 2009-06-19
14
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Launchpad itself
Low
Unassigned

Bug Description

One of the queries is to retrieve the LibraryFileAliases individually and the other is to retrieve the LibraryFileDownloadCount (to get the day of the last download) for each LFA.

OOPS-1263EA751 shows them, but in there you'll see 2 times more LFDC queries, which are not issued since bug 388082 was fixed.

Guilherme Salgado (salgado) wrote :

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;

Changed in launchpad-registry:
status: New → Triaged
Curtis Hovey (sinzui) on 2009-06-24
Changed in launchpad-registry:
importance: Undecided → Low
Curtis Hovey (sinzui) on 2010-12-04
tags: added: releases
Curtis Hovey (sinzui) on 2012-11-19
tags: added: tech-debt
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers