NULL in MySQL (Performance & Storage)

by

It depends on which storage engine you use.

In MyISAM format, each row header contains a bitfield with one bit for each column to encode NULL state. A column that is NULL still takes up space, so NULL’s don’t reduce storage. See https://dev.mysql.com/doc/internals/en/myisam-introduction.html

In InnoDB, each column has a “field start offset” in the row header, which is one or two bytes per column. The high bit in that field start offset is on if the column is NULL. In that case, the column doesn’t need to be stored at all. So if you have a lot of NULL’s your storage should be significantly reduced.
See https://dev.mysql.com/doc/internals/en/innodb-field-contents.html

EDIT:

The NULL bits are part of the row headers, you don’t choose to add them.

The only way I can imagine NULLs improving performance is that in InnoDB, a page of data may fit more rows if the rows contain NULLs. So your InnoDB buffers may be more effective.

But I would be very surprised if this provides a significant performance advantage in practice. Worrying about the effect NULLs have on performance is in the realm of micro-optimization. You should focus your attention elsewhere, in areas that give greater bang for the buck. For example adding well-chosen indexes or increasing database cache allocation.

More Related Contents:

  1. Find records from one table which don’t exist in another
  2. How to use GROUP BY to concatenate strings in MySQL?
  3. SQL is null and = null [duplicate]
  4. Best way to test if a row exists in a MySQL table
  5. Delete from two tables in one query
  6. Why would an IN condition be slower than “=” in sql?
  7. Fulltext Search with InnoDB
  8. MySQL dump by query
  9. MySQL’s now() +1 day
  10. Delete sql rows where IDs do not have a match from another table
  11. Rewriting mysql select to reduce time and writing tmp to disk
  12. Sorting null values after all others, except special
  13. How do I convert from BLOB to TEXT in MySQL?
  14. Can you access the auto increment value in MySQL within one statement?
  15. Which SQL query is better, MATCH AGAINST or LIKE?
  16. What does DELIMITER // do in a Trigger?
  17. Sort NULL values to the end of a table
  18. Mysql: how to select groups having certain values?
  19. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  20. How to create a multi-tenant database with shared table structures?
  21. MySQL #1140 – Mixing of GROUP columns
  22. MySQL: GROUP_CONCAT with LEFT JOIN
  23. Boolean vs tinyint(1) for boolean values in MySQL
  24. MySQL CONCAT returns NULL if any field contain NULL
  25. Find rows that have the same value on a column in MySQL
  26. How to change string date to MySQL date format at time of import of CSV using MySQL’s LOAD DATA LOCAL INFILE
  27. MySQL SELECT x FROM a WHERE NOT IN ( SELECT x FROM b ) – Unexpected result
  28. MySql bulk load command line tool
  29. ALTER TABLE without locking the table?
  30. MySQL: Typecasting NULL to 0

Leave a Comment