Newsletter #70: Indexing Columns With Null

[DaveB]

in What Was Larry Thinking? #70 it is stated that "You should not index colums that: Allow NULL values"

 

Could you please explain the reason for this?

[Antonio Conte]

It's bad for performance. Why that is is to technical for me to delve into, but my professor used an hour explaining why during a lesson some years ago. I took the hint...

 

The other reason is because allowing null values is often an indicator of bad db design. If you have columns with lots of null values, you should probably normalize it differently. The way to do it is to add another table and join the data in those cases.

 

An example:

Person ( id, firstName, middleName, lastName, ....)

 

The middleName column will obviosly have a lot of null values, because not everyone has a middle name. A way to solve it is to break it up:

 

Person (id, firstName, lastName, ....)

MiddleName (id, middleName)

 

When you query, you simply join it:

SELECT p.*, m.middleName 
FROM Person as p
LEFT OUTER JOIN MiddleName AS m ON (p.id = m.id)