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.

2 Answers 2


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.

  • 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
    Commented Mar 21, 2012 at 19:58
  • Hi, you said having 20-30 columns usually indicates poor design. 1- Do foreign key columns count? 2- I have a table with 5 foreign keys that one of them can have value (Photo table) when i read about indexing nullable columns, i decided to create 5 join tables, should i do that?
    – Arash
    Commented Oct 20, 2021 at 7:12

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

  • 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. Commented Apr 1, 2016 at 10:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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