Mysql – Does a NULL DATE use as much storage as a value

datedatetimeinnodbmariadb-10.3MySQL

I need to store a DATETIME value that may or may not have a date.

My options are:

| starts DATETIME     |
|---------------------|
| 0000-00-00 12:38:06 |

| starts_date DATE | starts_time TIME |
|------------------|------------------|
| NULL             | 12:38:06         |

If a NULL DATE value takes 0 storage, I may save a few bytes on most records (8 bytes on full DATETIME vs 3 bytes on just TIME). However, the following pages say that NULLs still take as much storage as the actual values on the fixed length columns, such as CHAR vs. VARCHAR. Is the DATE considered fixed-length too?

The byte size of NULL Decimal(19,4) or DATETIME (this is for SQL Server though)
Does an empty column value occupy same storage space as a filled column value

Best Answer

If you are using MyISAM, don't.

If you found that DATETIME is 8 bytes, you are using an old version of MySQL; it takes only 5 bytes now. Upgrade.

"Fixed" is slightly important for MyISAM; rarely important for InnoDB. So don't worry. In particular, don't worry unless you have a billion rows.

Related Solutions

Mysql – Are two indexes needed

An index can seek by a subset of characters, as long as you're searching from the left. E.g., "Inter%" can seek, "%net" will not.

However, the first character is not necessarily the character under which the article would be sorted. "The Internet" should go under "I", not "T". You probably need two fields, DisplayTitle and SortTitle; a single-character index on the latter may be worthwhile, but most likely a full-length index will be just fine.

Indexes are typically B-trees, and a seek will jump to the right location about equally quickly whether you have 10 or 100 entries per page. Scans are another matter, but I'd start with the simplest solution and add an extra index only if performance proves inadequate in practice.

SQL Server Update Column with Datetime – How to Use Row Values

It looks like you have at least two different regional date formats there, possibly USA and Europe? You could just split the updates by languages...

something like this, which uses the language setting to control isdate and convert.

set language english;
update test_dates 
set ToDateTime = convert(datetime,ContainedData)
where AID='123' and PID='2' and isdate(ContainedData)=1;

set language british;
update test_dates 
set ToDateTime = convert(datetime,ContainedData)
where AID='123' and PID='2' and isdate(ContainedData)=1;

If the table is too large to update in big chunks, then you should look at wrapping that in a loop with a row limit and a transaction around each update and repeat until zero rows have been updated.

Best Answer

Related Solutions

Mysql – Are two indexes needed

SQL Server Update Column with Datetime – How to Use Row Values

Related Question