MySQL – Datetime vs Date and Time

mysql

I generally use datetime field to store created_time updated time of data within an application.

But now i have come across a database table where they have kept date and time separate fields in table.

  1. So what are the schema in which two of these should be used and why?
  2. What are pros and cons attached with using of two?

Best Answer

There is a huge difference in performance when using DATE field above DATETIME field. I have a table with more then 4.000.000 records and for testing purposes I added 2 fields with both their own index. One using DATETIME and the other field using DATE.

I disabled MySQL query cache to be able to test properly and looped over the same query for 1000x:

SELECT * FROM `logs` WHERE `dt` BETWEEN '2015-04-01' AND '2015-05-01' LIMIT 10000,10; DATETIME INDEX: 197.564 seconds.

SELECT * FROM `logs` WHERE `d` BETWEEN '2015-04-01' AND '2015-05-01' LIMIT 10000,10; DATE INDEX: 107.577 seconds.

Using a date indexed field has a performance improvement of: 45.55%!!

So I would say if you are expecting a lot of data in your table please consider in separating the date from the time with their own index.

Related Question