refer to complex expression value in where clause

Question

I have a query where I have a complex date expression as one of the columns.

SELECT 
    date_column + INTERVAL( complex_jimmy_jam ) DAY AS complex_date_calculation
FROM table

I want to refer to that column in the where clause

WHERE complex_date_calculation < NOW()

But mysql chokes on it.

1054: Unknown column 'complex_date_calculation' in 'where clause'

One way to do this is to wrap it in a sub-select

SELECT * FROM ( 
    SELECT 
        date_column + INTERVAL( complex_jimmy_jam ) DAY AS complex_date_calculation
    FROM table 
) AS alias
WHERE complex_date_calculation < NOW()

Is this the best way?

I could also re-do the calculation in the WHERE clause, but that seems dumb. Why have the database calculate that date twice? Or, will the optimizer store that value?

Answer 1

For optimal performance, you should index date_column and move the calculation to the other side of the comparator in the where clause, like so:

SELECT 
    date_column + INTERVAL( complex_jimmy_jam ) DAY AS complex_date_calculation
FROM table
WHERE date_column < NOW() - INTERVAL( complex_jimmy_jam ) DAY

This way the index on date_column can be used to satisfy the where clause.

Answer 2

What I want is HAVING:

SELECT 
    date_column + INTERVAL( complex_jimmy_jam ) DAY AS complex_date_calculation
FROM table
HAVING complex_date_calculation < NOW()