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 only takes a minute to sign up.
Sign up to join this community
As I explored the clustered B-tree index system of InnoDB, I think that the presence of many NULL (or small) columns has no significant effect on InnoDB performance.
Does the presence of excess columns slow down mysql performance?
P.S. I tried to practically test, but no significant effect. However, I think it should be compared under heavy load. This is the reason that I am curious to learn about technical reasoning on this matter.
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
NOT NULL by default and only otherwise when you expect NULL values, is data integrity rather than size or performance. If the column should allow NULLs because the system you are modelling allows for that property to be unknown of not applicable then declare it NULLable otherwise declare it NOT NULL and let the database enforce the constraint for you.
Apr 1, 2016 at 10:40