Comment 4 for bug 315458

The above query is to optimize the slow query in OOPS-1083D1281. The other 4 OOPSes are failing with a later query being too slow.

An optimized version of that query is:

SELECT Build.*
FROM Build
WHERE
    Build.distroarchseries IN (
        81, 87, 80, 82, 83, 84, 85, 86, 77, 75, 78, 73, 76, 79, 74, 66, 70, 67, 71, 68, 72, 69, 59,
        60, 61, 62, 65, 63, 64, 47, 49, 51, 52, 48, 50, 38, 35, 39, 36, 40, 37, 16, 21, 17, 28, 18,
        19, 14, 26, 13, 25, 15, 24, 8, 7, 23, 9, 22, 11, 10, 12)
    AND buildstate = 1
    AND datebuilt IS NOT NULL
    AND archive IN (
        SELECT Archive.id
        FROM
            Archive
            JOIN Distribution ON Archive.distribution = Distribution.id
            JOIN DistroSeries ON Distribution.id = DistroSeries.distribution
            JOIN DistroArchSeries ON DistroSeries.id = DistroArchSeries.distroseries
        WHERE
            Archive.purpose IN (1,4)
            AND DistroArchSeries.id IN (
                81, 87, 80, 82, 83, 84, 85, 86, 77, 75, 78, 73, 76, 79, 74, 66, 70, 67, 71, 68, 72, 69, 59,
                60, 61, 62, 65, 63, 64, 47, 49, 51, 52, 48, 50, 38, 35, 39, 36, 40, 37, 16, 21, 17, 28, 18,
                19, 14, 26, 13, 25, 15, 24, 8, 7, 23, 9, 22, 11, 10, 12)
        )
ORDER BY Build.datebuilt DESC
LIMIT 75 OFFSET 0;

I'm not sure if we need to check the distroarchseries in both places. The following is faster still:
SELECT Build.*
FROM Build
WHERE
    buildstate = 1
    AND datebuilt IS NOT NULL
    AND archive IN (
        SELECT Archive.id
        FROM
            Archive
            JOIN Distribution ON Archive.distribution = Distribution.id
            JOIN DistroSeries ON Distribution.id = DistroSeries.distribution
            JOIN DistroArchSeries ON DistroSeries.id = DistroArchSeries.distroseries
        WHERE
            Archive.purpose IN (1,4)
            AND DistroArchSeries.id IN (
                81, 87, 80, 82, 83, 84, 85, 86, 77, 75, 78, 73, 76, 79, 74, 66, 70, 67, 71, 68, 72, 69, 59,
                60, 61, 62, 65, 63, 64, 47, 49, 51, 52, 48, 50, 38, 35, 39, 36, 40, 37, 16, 21, 17, 28, 18,
                19, 14, 26, 13, 25, 15, 24, 8, 7, 23, 9, 22, 11, 10, 12)
        )
ORDER BY Build.datebuilt DESC
LIMIT 75 OFFSET 0;

Or even better:

SELECT Build.*
FROM Build, Archive
WHERE
    Build.archive = Archive.id
    AND Archive.purpose IN (1,4)
    AND buildstate = 1
    AND datebuilt IS NOT NULL
    AND Build.distroarchseries IN (
                81, 87, 80, 82, 83, 84, 85, 86, 77, 75, 78, 73, 76, 79, 74, 66, 70, 67, 71, 68, 72, 69, 59,
                60, 61, 62, 65, 63, 64, 47, 49, 51, 52, 48, 50, 38, 35, 39, 36, 40, 37, 16, 21, 17, 28, 18,
                19, 14, 26, 13, 25, 15, 24, 8, 7, 23, 9, 22, 11, 10, 12)
ORDER BY Build.datebuilt DESC
LIMIT 75 OFFSET 0;