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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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