We have a table with a lot of columns that are rarely used (most are NULL).

Can this slow down queries? More importantly, do the empty columns take a lot of space per row?

  • The answer depends on the storage engine and can be easily found from product documentation. – mustaccio Feb 6 '18 at 23:52

Rarely used columns may be a sign of bad normalization, something that, if they are never used could (and this is highly dependent on your structure, engine, table size, data types and access pattern) help improve disk space and consequently performance. If the tables are tiny to small, there may be hardly any difference, although it may result in cleaner/more elegant design.

However in modern versions of MySQL's default Engine, InnoDB, NULLs may take as little overhead as a single bit, as they can be stored as field header flags.

If you really want to know exactly the difference, I would encourage you to test the difference of performance/disk space by separating optional columns on a separate table and joining on query time.

See other related answers: https://stackoverflow.com/a/230923/342196

|improve this answer|||||

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.