What's the impact of NULL on MySQL tables? (InnoDB)

Question

Recently, I've been thinking if it's worth to have 1 table with perhaps a lot of NULL columns or if it's better to have more tables with no NULLs. I've heard that NULL isn't stored on InnoDB, so I was wondering if there is any downside or problem with having a bunch of rows with a lot of NULLs. I have always heard that common NULLs are bad, but I have never really learned why. By the way, those will be NULLs on foreign keys, if that matters at all.

On a second doubt, is there any performance issue when I'm using INNER JOIN on columns that have a lot of NULL? Like, if I have 4 foreign keys, and I'm going to do 4 INNER JOINs, but most likely only 1 of them is not NULL, is this going to affect perfomance? Thanks

Answer 1

See:

• Does mysql index null values?
• NULL in MySQL (Performance & Storage)

NULLs are indexed.

In InnoDB, you can reduce the storage requirements for your data row by using NULL.

Answer 2

In the book High Performance MySQL: Optimization, Backups, Replication, and More by Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy Zawodny, Arjen Lentz, and Derek J. Balling, it says table columns that allow NULLs make it harder for MySQL to optimize queries that refer to nullable columns. And when a nullable column is indexed, it requires an extra byte per entry and can cause a fixed-size index (such as an index on a single integer column) to be converted to a variable-sized one in MyISAM. They say when columns need to carry a missing value, to use 0 for integers, a special character or an empty string instead. Avoid NULL and NOT NULL whenever possible.