Null Values and Performance

Question

I always try to build the database avoiding any NULL values in my tables, and I have successfully had done this so far. (Hey, I am very new to database design.) But still there are times when saving user's data, there may be null values or empty strings.

Can you please guide me to get the effect of having NULL values or empty strings in a database, where SELECT is most used?

NOTE: Specifically for MySQL 5.5 or later.

• Philᵀᴹ: Can you explain the logic behind you trying to avoid NULL values?
• Sanuj: I am a beginner in this, As having NULL values use full table scan if we do a ORDER BY (if I am correct) and I am having all most all query on the table with ORDER BY clause That's why for the sake of Performance I try to avoid NULL Values or Empty strings and move them to another table using relation, so if there is NULL or Empty string then it will not have any entry of that information. BUT I am not sure about the performance part when there is no sense in keeping that info in another table
• Chris Travers: This is good. It encourages semantic clarity of data and means that nulls are always products of outer joins. I am all for avoiding nulls in values, although the tradeoff is in cross-column constraints which MySQL doesn't support well anyway.

Answer 1

Performance-wise I think there are a few issues you will run into on MySQL. The first one is that MySQL doesn't handle very large numbers of joins as well as one might like. This can cause performance issues. Also you do have extra table scans and that has a performance cost too.

Finally, and this is InnoDB specific, but every table is clustered on a primary key and the engine does not support scanning a table in physical order, so in this regard joins of large record sets is going to become a performance issue. At some point you end up with a sequential scan but this is a sequential index scan, traversing every node in a tree, and that leads to a lot of extra disk I/O.

• Sanuj: Thanks @Chris, As I mostly use InnoDB Engine and In case of breaking tables for the sake of not having NULL Values will also include many JOINS while retrieving and that will lead to the extra disk I/O as InnoDB will probably do a sequential index scan. Is that approximate to what you mean to say?