2 ms·

nobody does. MySQL, Oracle, MSSQL, you name it. All sux. That's why I prefer to always declare NOT NULL and have a DEFAULT value when I create tables. Treat the

nobody does. MySQL, Oracle, MSSQL, you name it. All sux. That's why I prefer to always declare NOT NULL and have a DEFAULT value when I create tables. Treat the default value as NULL and you'll increase performance a lot.

deathanatos 7y ago
PostgreSQL will happily use an index when looking up nulls. Given an index over an integer column, a query for a constant non-null value appears like: QUERY PLAN --------------------------------------------------------------------- Index Scan using foo_b_idx on foo (cost=0.29..8.30 rows=1 width=8) Index Cond: (b = 333) The exact same query plan is generated for a query looking for nulls: QUERY PLAN --------------------------------------------------------------------- Index Scan using foo_b_idx on foo (cost=0.29..8.30 rows=1 width=8) Index Cond: (b IS NULL) (That is, the condition on the scan is the only thing that differs.) (I would heavily suspect that both MSSQL and MySQL have similar behavior here; this is an easy optimization for a query planner.)
- Groxx 7y ago
  MySQL does too, yes, the comment is clearly incorrect: https://dev.mysql.com/doc/refman/8.0/en/is-null-optimization.html https://dev.mysql.com/doc/refman/8.0/en/is-null-optimization... And it has done so for many versions, 8 is just the default redirect I got. The very first sentence on that page is: >MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. It works effectively everywhere, with the restriction that it can only do one null-optimized comparison per index (e.g. `(a == 1 or a is null) AND (b == 1 or b is null)` will only optimize one piece). Which is a potentially-significant restriction that makes me curious about the internals... but it does exist, and typical for MySQL is clearly documented.

deathanatos 7y ago

PostgreSQL will happily use an index when looking up nulls. Given an index over an integer column, a query for a constant non-null value appears like: QUERY PLAN --------------------------------------------------------------------- Index Scan using foo_b_idx on foo (cost=0.29..8.30 rows=1 width=8) Index Cond: (b = 333) The exact same query plan is generated for a query looking for nulls: QUERY PLAN --------------------------------------------------------------------- Index Scan using foo_b_idx on foo (cost=0.29..8.30 rows=1 width=8) Index Cond: (b IS NULL) (That is, the condition on the scan is the only thing that differs.) (I would heavily suspect that both MSSQL and MySQL have similar behavior here; this is an easy optimization for a query planner.)

Groxx 7y ago
MySQL does too, yes, the comment is clearly incorrect: https://dev.mysql.com/doc/refman/8.0/en/is-null-optimization.html https://dev.mysql.com/doc/refman/8.0/en/is-null-optimization... And it has done so for many versions, 8 is just the default redirect I got. The very first sentence on that page is: >MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. It works effectively everywhere, with the restriction that it can only do one null-optimized comparison per index (e.g. `(a == 1 or a is null) AND (b == 1 or b is null)` will only optimize one piece). Which is a potentially-significant restriction that makes me curious about the internals... but it does exist, and typical for MySQL is clearly documented.