Bug #73396 Optimize BIT(X) column storage in rows and indexes
Submitted: 25 Jul 2014 16:48
Reporter: Simon Mudd (OCA) Email Updates:
Status: Open Impact on me:
Category:MySQL Server: Storage Engines Severity:S4 (Feature request)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: bit, efficient storage.

[25 Jul 2014 16:48] Simon Mudd
See: http://www.xaprb.com/blog/2006/04/11/bit-values-in-mysql/

Please consider making more efficient storage of multiple BIT(X) columns in a table.

How to repeat:
BIT(X) columns are stored in a rounded number of bytes. However multiple BIT(X) columns are not stored for efficiency in the smallest number of bytes they would occupy.  I think this is true in rows and also indexes containing multiple BIT(X) columns.

Suggested fix:
This type of column is often used for things like flags so it's quite likely that some tables may have a large number of this type of column. On large tables this may save some disk space in both the rows and any multi-column indexes using this type.

As stated in the blog post this sort of facility is available in other RDBMSes.

There should be no need to change any user interaction as access to the data should be unchanged.

Should a change like this be implemented be careful with "conversion issues" such as those with TIMESTAMP and DATETIME in MySQL 5.6 which under certain circumstances can have unexpected side-effects.