Proper Optional Parameters in MySQL

Question

If the value of @param is null, which is better to use:

1. WHERE t.column = COALESCE(@param, '')
2. WHERE t.column = IFNULL(@param, '')
3. WHERE (@param IS NULL OR t.column = @param)

Is the OR more expensive than comparing the column to a value that will return all values for the specified column? My understanding is that options 1 & 2 will force a full table scan every time, while #3 would not.

Answer 1

I recommend trying the three options, using EXPLAIN in front of your query and see what MySQL thinks as it relates to your table and indexes.

Answer 2

Option 2 looks like a syntax error; ISNULL only takes one parameter, and returns a boolean.

Options 1 and 3 could return different values. When @param is null, option 1 would return only rows where t.column is the empty string, where option 3 would return all rows. Either one would use a full table scan unless you have an index on t.column; even then, option 3 would use the full scan when @param is null.

Answer 3

3 is best because the optimizer will short-circuit that evaluation during the optimizer stage and eliminate the where clause.