SELECT
    fl.name feature_name,
    fp.id_feature,
    fv.id_feature_value,
    fvl.value,
    COUNT(DISTINCT p.id_product) nbr,
    lifl.url_name name_url_name,
    lifl.meta_title name_meta_title,
    lifvl.url_name value_url_name,
    lifvl.meta_title value_meta_title,
    psi.price_min,
    psi.price_max,
    m.name
FROM
    ps_feature_product fp
INNER JOIN
    ps_product p
ON
    (p.id_product = fp.id_product)
LEFT JOIN
    ps_feature_lang fl
ON
    (
        fl.id_feature = fp.id_feature AND fl.id_lang = 2
    )
INNER JOIN
    ps_feature_value fv
ON
    (
        fv.id_feature_value = fp.id_feature_value AND(
            fv.custom IS NULL OR fv.custom = 0
        )
    )
LEFT JOIN
    ps_feature_value_lang fvl
ON
    (
        fvl.id_feature_value = fp.id_feature_value AND fvl.id_lang = 2
    )
LEFT JOIN
    ps_layered_indexable_feature_lang_value lifl
ON
    (
        lifl.id_feature = fp.id_feature AND lifl.id_lang = 2
    )
LEFT JOIN
    ps_layered_indexable_feature_value_lang_value lifvl
ON
    (
        lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = 2
    )
INNER JOIN
    ps_product_shop product_shop
ON
    (
        product_shop.id_product = p.id_product AND product_shop.id_shop = 1
    )
INNER JOIN
    `ps_layered_price_index` psi
ON
    (
        psi.id_product = p.id_product AND psi.id_currency = 2 AND psi.id_shop = 1
    )
LEFT JOIN
    `ps_manufacturer` m
ON
    (
        m.id_manufacturer = p.id_manufacturer
    )
WHERE
    product_shop.`active` = 1 AND product_shop.`visibility` IN("both", "catalog") AND fp.id_feature = 9 AND p.id_product IN(
    SELECT
        id_product
    FROM
        ps_category_product cp
    INNER JOIN
        ps_category c
    ON
        (
            c.id_category = cp.id_category AND c.id_category = 13 AND c.active = 1
        )
) AND p.id_product IN(
SELECT
    id_product
FROM
    ps_feature_product fp
WHERE
    fp.`id_feature_value` = 39
) AND p.id_product IN(
SELECT
    id_product
FROM
    ps_feature_product fp
WHERE
    fp.`id_feature_value` = 18
) AND p.id_product IN(
SELECT
    id_product
FROM
    ps_feature_product fp
WHERE
    fp.`id_feature_value` = 13
) AND p.id_product IN(
SELECT
    id_product
FROM
    ps_feature_product fp
WHERE
    fp.`id_feature_value` = 44
) AND p.id_product IN(
SELECT
    id_product
FROM
    ps_feature_product fp
WHERE
    fp.`id_feature_value` = 3186
)
GROUP BY
    fv.id_feature_value
ORDER BY
    fv.position

... AND
/*
-- removing any of the six product filters makes
-- the query a lot faster
p.id_product IN(
    SELECT
        id_product
    FROM
        ps_feature_product fp
    WHERE
        fp.`id_feature_value` = 18
)
*/
AND ...