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.
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.
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?
NULLvalues use full table scan if we do aORDER BY(if I am correct) and I am having all most all query on the table withORDER BYclause That's why for the sake of Performance I try to avoidNULLValues 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 theperformance partwhen there is no sense in keeping that info in another table