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.

| improve this answer | |
  • 3
    very useful information. However, I disagree that table 20-30 columns is due to poor design; sometimes we need it, like storing a package of statistical data. – Googlebot Mar 21 '12 at 19:58

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

| improve this answer | |
  • Of course the main reason for declaring columns 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. – David Spillett Apr 1 '16 at 10:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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