+download issues too many queries to retrieve time of last download
Bug #388082 reported by
Guilherme Salgado
This bug affects 1 person
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Launchpad itself |
Fix Released
|
High
|
Guilherme Salgado |
Bug Description
https:/
There's an easy fix that will cut that number down by 2/3, but we'd still be issuing around 200 queries, which is almost the total number of queries issued by that page before the download counts appeared there.
summary: |
- +download issues too many queries to retrieve download counts + +download issues too many queries to retrieve time of last download |
description: | updated |
Changed in launchpad-registry: | |
status: | Triaged → In Progress |
To post a comment you must log in.
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 LibraryFileAlia s.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 es.name, ias.id AS signature_id, aseFile. description, Alias.id, Alias.filename, Content. filesize, product = Product.id productseries = ProductSeries.id milestone = Milestone.id ile.productrele ase = ProductRelease.id ile.libraryfile ent.id = LibraryFileAlia s.content loadCount loadCount. libraryfilealia s = LibraryFileAlias.id ile.signature
Product.name,
ProductSeri
SignatureAl
Milestone.name,
ProductRele
LibraryFile
LibraryFile
LibraryFile
SUM(count),
MAX(day) AS last_downloaded
FROM
Product
JOIN ProductSeries ON ProductSeries.
JOIN Milestone ON Milestone.
JOIN ProductRelease ON ProductRelease.
JOIN ProductReleaseFile
ON ProductReleaseF
JOIN LibraryFileAlias
ON LibraryFileAlias.id = ProductReleaseF
JOIN LibraryFileContent
ON LibraryFileCont
LEFT OUTER JOIN LibraryFileDown
ON LibraryFileDown
LEFT OUTER JOIN LibraryFileAlias AS SignatureAlias
ON SignatureAlias.id = ProductReleaseF
WHERE
Product.name = 'bzr'
GROUP BY es.name, aseFile. description, Alias.id, Alias.filename, Content. filesize
Product.name,
ProductSeri
signature_id,
Milestone.name,
ProductRele
LibraryFile
LibraryFile
LibraryFile
ORDER BY es.name, Alias.filename;
ProductSeri
Milestone.name,
LibraryFile