Using coalesce on an index column

Does doing a COALESCE on an indexed columns in a WHERE clause e.g. WHERE COALESCE(table1.indexed_column, '9999-01-01') > table2.other_date_column affect indexing in a bad way?

I think that it should but in this case how should I convert my where clause?

If you afraid that the condition will keep the optimizer from using an index on the columns, you are right, the use of functions in most DBMS has this effect. There are some exceptions to the rule but not in MySQL.

If you want indexes to be considered (and possibly used), you have to conevrt the condition to one without functions applied to columns.

Your condition, (if table2.other_date_column is not nullable), is equivalent to:

(  table1.indexed_column > table2.other_date_column 
 OR table1.indexed_column IS NULL )

StackExchange Database Administrators Q#52219, answer score: 4