Mysql Update IN condition

Question

I can't succeed to have a "quick" (< 6 minutes) with a query : (the select himself take 10 sec max)

UPDATE
    peel_utilisateurs AS PU
SET
    PU.online = 3,
    PU.date_desactivation = NOW()
WHERE
    PU.id_utilisateur IN(
    SELECT
        PC.id_utilisateur
    FROM
        peel_commandes AS PC
    GROUP BY
        PC.id_utilisateur
    HAVING
        MAX(PC.o_timestamp) <(NOW() - INTERVAL 1 YEAR)
    ORDER BY
        `PC`.`o_timestamp`
    DESC
        )

Someone Could help me ? :)

Thank you,

Answer 1

You can try to change the IN operator with a JOIN. In general, as described here, the IN operator is slower than a JOIN for large datasets.

UPDATE
    peel_utilisateurs AS PU
    INNER JOIN peel_commandes AS PC 
    ON 
        PU.id_utilisateur = PC.id_utilisateur 
        AND PC.o_timestamp <(NOW() - INTERVAL 1 YEAR)

SET
    PU.online = 3,
    PU.date_desactivation = NOW()