1251064 - Retrieving products from /rest/product is too slow when component flag types are included

Status: NEW

(bugzilla.mozilla.org :: API, defect)

Description

[Kohei Yoshino [:kohei]]

Loading https://bugzilla.mozilla.org/rest/product?type=accessible takes 45 seconds for me, maybe because of the duplicated flag_types?

Comment 2

[Kohei Yoshino [:kohei]]

If excluding the component flag types, the response will be much faster.

https://bugzilla.mozilla.org/rest/product?type=accessible&exclude_fields=components.flag_types

Comment 3

[Dylan Hardison [:dylan] (he/him)]

Can we just remove flags from the API? That's the bottle neck, and it's hard to optimize because of the include/exclude nature of the flag implementation.

Comment 4

[Dylan Hardison [:dylan] (he/him)]

The following SQL query is executed 2170 times, at a cost of 6ms per call. Because of the inclusion/exclusion logic I'm not sure we could do the same thing with 1 call.

SELECT 
    flagtypes.id
FROM
    flagtypes
        INNER JOIN
    flaginclusions AS i ON flagtypes.id = i.type_id
        LEFT JOIN
    flagexclusions AS e ON (flagtypes.id = e.type_id
        AND (e.component_id = 2113
        OR e.component_id IS NULL)
        AND ((e.product_id = 127)
        OR e.product_id IS NULL))
WHERE
    1 = 1
        AND (i.product_id = 127
        OR i.product_id IS NULL)
        AND (i.component_id = 2113
        OR i.component_id IS NULL)
        AND e.type_id IS NULL LIMIT 1000