Clever Geek Handbook

Indexing Date and Time in MySQL

What is the best way to index datetime in MySQL? Which method is faster:

  • Store datetime as double (via unix timestamp)
  • Store datetime as date and time

An application that generates timestamp data can output any format. Unfortunately datetime will be the key for this particular data structure, so speed will matter.

Also, is it possible to make an index for the expression? For example, an index on UNIX_TIMESTAMP(mydate)

, where mydate is a field in a table and UNIX_TIMESTAMP is a mysql function. I know Postgres can do this . I think there should be a way in mysql too.

+2


source to share


2 answers


I don't think any method will be much faster than the other, but if you choose 'datetime' it will be much easier to work with standard time functions.



MySQL does not support functional indexes .

+3


source


I think using an integer will be faster because it where datetime >= '2010-09-30 01:31:41'

will never hit the index. However, it where intTimestamp >= 237492347

will be.



-1


source






More articles:


All Articles