Take the 2-minute tour ×
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's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
1  
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. –  All Mar 21 '12 at 19:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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