Jump to content
Larry Ullman's Book Forums

Newsletter #70: Indexing Columns With Null

Recommended Posts

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?

Share this post

Link to post
Share on other sites

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)

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...