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.
Best Answer
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.