MySQL – Which Column Type is Better: VARCHAR or DATETIME?

database-designMySQLperformancequery-performance

I have a Script that uses Date to pick the day name and number of it, Something like the following

2018-06-04 =becomes=> Monday(04, June)
2018-06-04 =or becomes=> Monday(04) // In no need for Month

So What I do is creating a Column in the table using DATETIME datatype, Then I set the values like this

000-06-04 //case1
000-00-04 //case2

Is that a proper way to do this, Or is it better if I use VARCHAR(18) datatype and write it directly like this

Monday(04, June) //case1
Monday(04) //case2

Using DATETIME requires me to use DATE_FORMAT() later on, And using VARCHAR(18) doesn't.

SELECT DATE_FORMAT(date, '%W(%d, %m)') FROM table //case1
SELECT date FROM table //case2

I'm don't care about ease of use But performance.

Best Answer

As stated by manual DATE type internally represented by 3 bytes that is smallest possible for the required type of data. TIMESTAMP is represented internally as BIGINT seconds since epoch (4 bytes) but TIMESTAMP is always forced to be converted to/from the UTC if server's TZ is not UTC. That can cause significant overhead. DATE is compatible with date-related functions like week() or monthname() so it's look like the best choice.

DATE is far more efficient for search/sort/join/group/between than VARCHAR(18) so VARCHAR() isn't even an option.

Related Question