I’m working with a MySQL database and have a question about how indexes handle NULL values. In my current table, there’s an indexed INTEGER column that contains the value 0 for approximately 90% of all records. I’m wondering if I should change these zero values to NULL instead.
My thinking is that if MySQL doesn’t include NULL values in its indexes, then switching from 0 to NULL for these rows would dramatically reduce the index size by around 90%. This could potentially improve query performance and reduce storage space.
Can someone explain whether MySQL actually stores NULL values in indexes? Would making this change achieve the space savings I’m hoping for?
MySQL actually includes NULL values in most indexes, so this strategy won’t give you the benefits you’re expecting. I hit this same misconception years ago while optimizing a large user table. Here’s the thing - MySQL’s InnoDB engine stores NULLs in indexes using a special bit vector mechanism. Switching your 0s to NULL won’t shrink index size much. NULLs might even take slightly more space because of the extra metadata needed to track them. You’d be better off creating a partial or filtered index if your queries usually exclude those 0 values. Problem is, MySQL doesn’t support partial indexes like PostgreSQL does. You’ll need workarounds through query optimization or table structure changes. With 90% zeros, you’d probably get more benefit from restructuring your queries than changing value types.