Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
Can you explain the logic behind you trying to avoid NULL values? –  Phil Oct 9 '12 at 6:56
    
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 –  Sanuj Oct 9 '12 at 7:05
1  
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. –  Chris Travers Oct 9 '12 at 10:13
add comment

1 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.

share|improve this answer
    
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? –  Sanuj Oct 10 '12 at 3:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.