Performance difference using IF function vs AND in MySQL WHERE clause-CodePudding

I am writing a query that should find rows where the created column is older than x hours ago. However, "x" will be variable depending on another column value.

I have two ways of writing this and was wondering if there will be any performance differences between them:

SELECT
    *
FROM
    table
WHERE
    created > NOW() - INTERVAL IF(column1 > 500, 365, 750) DAY

SELECT
    *
FROM
    table
WHERE
    column1 > 500 
        AND created > NOW() - INTERVAL 365 DAY
    OR
    column1 <= 500 
        AND created > NOW() - INTERVAL 750 DAY

CodePudding user response：

Add an index on column1 and use UNION to combine the two conditions.

Even better might be to have a composite index on (column1, created), so both parts of the condition can be done entirely within the index.

SELECT *
FROM table
WHERE column1 > 500 AND created > NOW() - INTERVAL 365 DAY

UNION ALL

SELECT *
FROM table
WHERE column1 <= 500 AND created > NOW() - INTERVAL 750 DAY