Comment 5 for bug 594247

The following query should be the same and performs in <1s:

SELECT COUNT(DISTINCT BugTask.id)
FROM Bug
JOIN BugTask ON Bug.id = BugTask.bug
LEFT OUTER JOIN Product ON BugTask.product = Product.id
JOIN StructuralSubscription ON (
    BugTask.product = StructuralSubscription.product
    OR (
        BugTask.distribution = StructuralSubscription.distribution
        AND (
            BugTask.sourcepackagename IS NULL
            OR BugTask.sourcepackagename
                = StructuralSubscription.sourcepackagename
            )
        )
    OR BugTask.distroseries = StructuralSubscription.distroseries
    OR BugTask.milestone = StructuralSubscription.milestone
    OR BugTask.productseries = StructuralSubscription.productseries
    OR Product.project = StructuralSubscription.project
    )
WHERE
    BugTask.importance = 40
    AND BugTask.distribution = 1
    AND (
        (BugTask.status = 10)
        OR (BugTask.status = 15) AND (
            Bug.date_last_message IS NOT NULL
            AND BugTask.date_incomplete <= Bug.date_last_message
            )
        OR (BugTask.status = 15) AND (
            Bug.date_last_message IS NULL
            OR BugTask.date_incomplete > Bug.date_last_message)
        OR (BugTask.status = 20) OR (BugTask.status = 21)
        OR (BugTask.status = 22) OR (BugTask.status = 25)
        )
    AND Bug.duplicateof is NULL
    AND BugTask.distroseries is NULL
    AND BugTask.productseries is NULL
    AND StructuralSubscription.subscriber = 458651
    AND (Bug.private = FALSE OR EXISTS (
        SELECT BugSubscription.bug FROM BugSubscription, TeamParticipation
        WHERE
            TeamParticipation.person = 972620
            AND BugSubscription.person = TeamParticipation.team
            AND BugSubscription.bug = Bug.id
        ))

It is still horrible though - the data model makes joining BugTask with StructuralSubscription problematic. I suspect we need to define a BugTarget (or just Target) and link StructuralSubscription and BugTask to that instead of (distribution OR distroseries OR (distribution AND sourcepackagename) OR Project OR Product OR Milestone OR productseries).