Mysql column with null values ​​- what are the space requirements?

advertisements

I have a table with quite a lot entries. I need an additional column with an integer value or null. The thing is that only very few rows will have that field populated.

So i wonder whether its better to create a seperate table where i link the entries in an 1:1 relation.

I know one integer entry takes 4 bytes in mysql/myisam. If I have the column set to allow null values, and only 100 of 100 000 rows have the field populated, will the rest still consume 4 bytes for every null value?

Or is mysql intelligent enough to set the value where it is populated and just regard everything as null, where nothing is set?

This depends on the ROW_FORMAT value you give when you create your table.

Before version 5.0.3, the default format is set to "REDUNDANT" : any fixed-length field will use the same space, even if it's value is NULL.

Starting with version 5.0.3, the value is set to "COMPACT" : NULL values will never use any space in your database.

You can do an ALTER TABLE to be sure to use the correct format :

ALTER TABLE ... ROW_FORMAT=COMPACT

More details here : http://dev.mysql.com/doc/refman/5.1/en/data-size.html

Related Articles

Update columns with null values

Hi I want to update a table as followings update userloginstats set logouttime= sysdate where logouttime= null; But it did not update the columns with null values. what is wrongChange it to ...where logouttime is null; ^^^^^^^ NULL is a special value

Columns with null values ​​in SQL Server vs. Oracle

In Oracle: ='' and 'is null' return NULL Values SELECT COL1 FROM TABLE WHERE COL1='' --> RETURN COL1 WITH NULL VALUES SELECT COL1 FROM TABLE WHERE COL1 IS NULL --> RETURN COL1 WITH NULL VALUES Both queries return the same set of rows. In Microsoft S

using the NOT IN operator on a column with null values

I have a table employee which has a column company which has all null values Now, I want to use a NOT IN operator for the company name as select * from employee where employee.company NOT IN ('ABC', 'DEF') Now technically this should not alter the re

Sort DataGridColumn with null values ​​always at the bottom

I want a column in my DataGrid to sort with null values always at end. I've tried to do this by following this (part 1) and this (part 2). But my custom sort doesn't work like I want. Thats my compare method for my column : private int NullableDateTi

Finding Columns with Null Values ​​in Teradata

I would like to find the columns in a table that has a null value in it. Is there a system table that have that information?To find columns where "null" values are allowed try... select * from dbc.columns where databasename = 'your_db_name' and

What are the mechanisms required for a reliable UDP layer?

I've been working on writing my own networking engine for my own game development side projects. This requires the options of having unreliable, reliable, and ordered reliable messages. I have not, however, been able to identify all of the mechanisms

Updating an indexed column with NULL values

I have a dups index on a DATETIME YEAR TO FRACTION column named TouchTime, which I use as a timestamp to locate the most recently inserted or updated row for the current workday. I recently added this column to a table with ~800K rows. An average of

How to index a date column with null values?

How should I index a date column when some rows has null values? We have to select rows between a date range and rows with null dates. We use Oracle 9.2 and higher. Options I found Using a bitmap index on the date column Using an index on date column