CODEWITHSUNDEEP
mysql
Alessandro Romeo
Alessandro Romeo

Reputation: 108

Optimize query repetitions

How can I remove repetitions from this query?

SELECT
    *, UNIX_TIMESTAMP(
        STR_TO_DATE(
            CONCAT(
                IFNULL(expected_release_day, 1),
                ' ',
                IFNULL(
                    expected_release_month,
                    IFNULL(
                        3 * expected_release_quarter,
                        1
                    )
                ),
                ' ',
                expected_release_year
            ),
            '%d %m %Y'
        )
    ) calc
FROM
    games
WHERE
    expected_release_year > 0 && UNIX_TIMESTAMP(
        STR_TO_DATE(
            CONCAT(
                IFNULL(expected_release_day, 1),
                ' ',
                IFNULL(
                    expected_release_month,
                    IFNULL(
                        3 * expected_release_quarter,
                        1
                    )
                ),
                ' ',
                expected_release_year
            ),
            '%d %m %Y'
        )
    ) > UNIX_TIMESTAMP(NOW())
ORDER BY
    calc

Upvotes: 1

Views: 44

Answers (1)

Mihai
Mihai

Reputation: 26794

One method is to use a subquery

SELECT *,calc FROM
(
    SELECT *, UNIX_TIMESTAMP(
        STR_TO_DATE(
            CONCAT(
                IFNULL(expected_release_day, 1),
                ' ',
                IFNULL(
                    expected_release_month,
                    IFNULL(
                        3 * expected_release_quarter,
                        1
                    )
                ),
                ' ',
                expected_release_year
            ),
            '%d %m %Y'
        )
    ) calc
FROM
    games)x
WHERE
    expected_release_year > 0 && calc > UNIX_TIMESTAMP(NOW())
ORDER BY
    calc

Upvotes: 1

Related Questions