Welcome to WebmasterWorld Guest from 34.229.24.100

Forum Moderators: open

MySQL: should I use DATE, INT(8), or other?

     
5:03 am on May 17, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1116
votes: 110


I have a table with about 32 million rows, and growing daily.

One column in this field is a UNIQUE column for the date. I'm not storing time, just the day itself. It's used to count the number of impressions for each ID per day, so if the day already exists then it increments another column.

I've been using INT(8) for this column, storing dates like:

20190516

I tried changing it to DATE in the theory of saving storage space and making the index faster, but the storage size was MUCH larger. I'm of the understanding that an INT(8) takes 4 bytes while a DATE takes 3, but the index size went from 561.9M to 771.1M!

I'm running a smidge low on storage on my server, and I'm trying to push off upgrading as long as possible, so storage size does matter. But if it's faster to process for some reason, then I can justify 200M.

Or is there a better type to store the date than these?
8:16 am on May 17, 2019 (gmt 0)

Full Member

5+ Year Member

joined:Aug 16, 2010
posts:257
votes: 21


Did you use the inplace or copy algorithm with the alter statement? Perhaps you need to run optimize table if you used the copy algo to see the real size of the index.
5:59 pm on May 17, 2019 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member tangor is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Nov 29, 2005
posts:9927
votes: 974


Second optimize (or repair).