Has null instead of integer/string in the mysql WHERE clause any impact on the performance?-CodePudding

for a following query :

SELECT some_value 
FROM some_table 
WHERE param_one='62627' 
AND param_two='1' 
AND param_three=QUESTIONABLE_VALUE

Does it have any impact on the performance if QUESTIONABLE_VALUE is a null or an integer/string?

CodePudding user response：

IS NULL Optimization. MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.

You must have a composite index

`idx` (`param_one`,`param_two`,`param_three`)

Use EXPLAIN to check how the optimizer behave on different datatypes

CodePudding user response：

If you have a parameter that may be null or may be non-null, and you want to match data that is the same, you would use the <=> operator (null-safe comparison).

SELECT some_value 
FROM some_table 
WHERE param_one='62627' 
AND param_two='1' 
AND param_three <=> QUESTIONABLE_VALUE

With =, the result will be null if either operand is null.