Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
You think it should do what? It seems like some verb was removed from the sentence. –  ypercube Oct 25 '13 at 14:39
    
@ypercube:Thank you.Updated OP –  Jim Oct 25 '13 at 14:44
    
COALESCE is a function and not SARGable. Ergo, it won't use an index directly. –  JNK Oct 25 '13 at 14:45
    
Are the 2 columns in the same table or in 2 different ones? –  ypercube Oct 25 '13 at 14:45
    
@ypercube:different tables.I will update OP.Good point –  Jim Oct 25 '13 at 14:55
add comment

1 Answer 1

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 )
share|improve this answer
    
Updated OP.The columns are from different tables –  Jim Oct 25 '13 at 14:56
    
1)How is the comparison with NULL going to work in your condition? 2) Hm... the column is not defined as not nullable but currently there are no NULL values in the table –  Jim Oct 25 '13 at 15:14
    
table1.indexed_column > table.other_date_column this returns NULL if table1.indexed_column is null. So what is the idea here? –  Jim Oct 25 '13 at 15:17
    
The first line is used if the table1.indexed_column is not null. If it is null, the 2nd line become true and the condition becomes NULL OR TRUE which is TRUE. –  ypercube Oct 25 '13 at 15:23
    
+1. Very smart I think –  Jim Oct 25 '13 at 15:48
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.