0

I'm sketching tables for event planner. All my event will have date and time, but the search will use only dates. The time will not be used for searching.

Will storing, searching, indexing rows using DATE be noticeably faster than using DATETIME format?

It's more convenient for me to store the event date and time in DATETIME type, but I'm more concerned about the speed.

ProgZi
  • 1,028
  • 3
  • 14
  • 25
  • How many records are you planning to have? Unless you have many millions of records, it s not likely to be a noticeable difference. – HLGEM Nov 17 '15 at 18:55
  • Yes for best performance on a equality search if you are only interested in the date part storing it with just date will have best performance. Not even sure an index can be used for equality searches if the time part needs to be stripped on every value first. – Magnus Nov 17 '15 at 18:57
  • I agree, unless this is something with an enormous number of records, there are probably other optimizations that would yield more benefit for your effort. – Joshua Morgan Nov 17 '15 at 18:57
  • You may be able to create an index on just the "date" portion http://stackoverflow.com/questions/10595037/is-it-possible-to-have-function-based-index-in-mysql thus having an index with just dates, while storing full date time. or use "Generated Columns" if your version supports it. – xQbert Nov 17 '15 at 19:01
  • @HLGEM I don't expect my table to be more than 500K rows. – ProgZi Nov 17 '15 at 19:09

1 Answers1

2

I have not benchmarked it, but based on what I know about the MySQL code I would expect the difference to be marginal under normal use. DATE uses 3 bytes for storage, while DATETIME uses 8 bytes. This difference will be significant in some abnormal cases, e.g. if the DATE/DATETIME column is the only one or one of the very few in a table that has millions of rows, or if you have a lot of such columns. Under normal usage the overhead of DATETIME will drown relative to other database operations. This would be the case in particular if you have good keys and are able to avoid table scans with thousands of unnecessary DATE/DATETIME comparisons. So I would say do not worry about it and just use DATETIME.

Sasha Pachev
  • 5,162
  • 3
  • 20
  • 20
  • 1
    For an index to be usable when having a `DateTime` columns the query would need to be written as `Where tbl.column < @nextDate and tbl.column > @prevDate` compared to `Where tbl.column = @date` if the column is of type `Date`. – Magnus Nov 17 '15 at 19:17