Are many NULL columns harmful in mysql InnoDB?

The only harm I could see is doing COUNT queries against large tables.

Doing SELECT COUNT(*) FROM mytable on an InnoDB table should produce a full table scan. However, think of what COUNT() actually does. The * represents a whole row. COUNT() can determine if non-NULL columns are present. The easiest way for that to happen it to make sure PRIMARY KEY columns are the first columns in ordinal position. PRIMARY KEY columns are always NOT NULL by definition. Thus, SELECT COUNT(*) FROM mytable would be just as fast as SELECT COUNT(1) FROM mytable.

I would not be worried about excessive columns as InnoDB places a cap on the number at 1000. Of course, IMHO having 20-30 columns in a table (regardless of Storage Engine) is too high, which indicates either poor design (begging for normalization) or column data that is just too big.

PostgreSQL kind of solves that be having TOAST tables. TOAST stands for The Outside Attribute Storage Technique. That manages column data that is way too big for normal row storage.

InnoDB has no mechanism like TOAST, so I expect row chaining of some sort in the .ibd files or within ibdata1. Notwithstanding, NULL columns will prevent the physical manifestation of oversized row data. Anyone could live with that.

As long as InnoDB tables are properly indexed, NULL columns would not be an issue. Besides, all non-unique indexes have internal rowids back to the Clustered Index (aka gen_clust_index). Therefore, well-tuned queries will always be accessing data via the Clustered Index.

According to Mysql Official Doc :

Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is NULL. You also save some storage space, one bit per column. If you really need NULL values in your tables, use them. Just avoid the default setting that allows NULL values in every column. http://dev.mysql.com/doc/refman/5.5/en/data-size.html