launchpad-dev team mailing list archive

[Jonathan Lange <jml@xxxxxxxxxxxxx>]

Sorry, I had meant to reply to all.

On Fri, Jan 29, 2010 at 9:53 AM, Jonathan Lange <jml@xxxxxxxxxxxxx> wrote:
> On Thu, Jan 28, 2010 at 7:30 PM, Abel Deuring
> <abel.deuring@xxxxxxxxxxxxx> wrote:
>> Hi Stuart, hi Jono,
>>
>
> Hello Abel,
>
>> Tom and myself started to work on sorting bugs/bugtasks by the age of
>> the youngest patch attachment (see
>> https://bugs.edge.launchpad.net/malone/+bug/506018 ,
>> https://bugs.edge.launchpad.net/malone/+bug/512500 ). Basically, this
>> means to add an option "sort by creation time of youngest patch
>> attachment" to IHasBugs.searchTasks().
>>
>> We are a bit concerned that a regulary query, where we sort by the
>> youngest patch via something like
>>
>>    ORDER BY (SELECT max(message.datecreated)
>>                FROM message, bugattachment
>>                WHERE bugattachment.message=message.id AND
>>                    bugattachment.bug=<bug.id from main query> AND
>>                    bugattachment.type=1)
>>
>> in a query like "SELECT BugTask.whatever FROM BugTask,... WHERE...",
>> might easily lead to timeouts when this sorting is used for Ubuntu
>> bugs... (Tom wanted to write such a query for a test on staging, so we
>> might soon have some data.)
>>
>
> Have you got data yet?
>
>> So we also considered to add a column date_created_youngest_patch to the
>> table Bug, where this column would be updated only by triggers for
>> changes of the table BugAttachment, like so:
>>
> ...
>> In other words, a deliberate denormalisation. What do you think, should
>> we start with a plain query without such a denormalsation, or do you
>> think that it is better to add the "cache column"
>> date_created_youngest_patch to the Bug table?
>>
>
> This is really Stuart's province. I don't have a strong opinion on this.
>
> However, it seems to me that we're getting increasingly frequent
> requests to denormalize data in order to make querying faster. Perhaps
> we need a general answer, or some other technology.
>
> jml
>