-2

At the start of a project, a DATETIME column made sense. But now, 5 years after, requierments changed the time part have never be used.

My table has about 100k lines and grows with about 500 lines per month.

Does it worth to convert the column with a type DATETIME to DATE?

  • I mean, does a DATETIME field takes more size compare to DATE field?
  • Can I expect some disk space saving after column conversion?
  • Is it more faster for the DB to search in a DATE field?

I am using MySQL 5.7

3

I mean, does a DATETIME field takes more size compare to DATE field?

That's true. DATE needs 3 bytes, DATETIME needs 5-8 bytes.

Is it more faster for the DB to search in a DATE field?

In most cases the difference is below the measuring accuracy.

Can I expect some disk space saving after column conversion?

I doubt. Clustering effect. The larger the row_size, the less likely it is that the table file size will change.

Approximate explaination (not accurate, idea only).

The data is stored in the table file not as stream, but in blocks. There is some percent range of data fill in a block - when the real percent comes out of it, the blocks splits or joins.

Imagine that

  • block size is 16 kbytes = 16384 bytes
  • record size is 100 bytes
  • percent range is 40-80%, average 60%

In general the block stores (16384 * 60%) / 100 = 98 records in average.

We change DATETIME without fractional part (5 bytes) to DATE (3 bytes). New record size is 100 - 5 + 3 = 98 bytes.

So the block now stores (16384 * 60%) / 98 = 100 records in average.

The amount of blocks decreases by 2% only. Taking into account that the percent range varies twice, the file size decrease probability is 2% higher than increase probability. I.e. in most cases yuou cannot predict the change in real storage size.

7
  • Thank you for the explanation. Very helpful.
    – sdespont
    Apr 4 '20 at 5:38
  • Can you confirm that in every cases, I have to run dev.mysql.com/doc/refman/5.7/en/optimize-table.html to recover the saved database real storage size? Or the releases size will be reused by InnoDB when possible?
    – sdespont
    Apr 4 '20 at 5:47
  • @sdespont Information that the size of the table file on disk will necessarily become minimal after running OPTIMIZE TABLE is at least doubtful.
    – Akina
    Apr 5 '20 at 19:54
  • @sdespont - I have explained in many Q&A that using OPTIMIZE TABLE on an InnoDB table is virtually always a waste of time.
    – Rick James
    Apr 14 '20 at 0:35
  • @RickJames Thank you for your comment! Could you point on the best Q&A link you wrote about this please?
    – sdespont
    Apr 14 '20 at 9:21
1

You could think about mysql 8 , it is faster than the 5.x versions and has a lot of new function, specially the window functions and Regular expression

1
  • Thank your for the link, helps me to understand the real storage size of a field.
    – sdespont
    Apr 4 '20 at 5:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.