Indexing datetime in MySQL

Question

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

Store the datetime as a double (via unix timestamp)
Store the datetime as a datetime

The application generating the timestamp data can output either format. Unfortunately, datetime will be a key for this particular data structure so speed will matter.

Also, is it possible to make an index on an expression? For example, index on UNIX_TIMESTAMP(mydate) where mydate is a field in a table and UNIX_TIMESTAMP is a mysql function. I know that Postgres can do it. I'm thinking there must be a way in mysql as well.

Mark Byers · Accepted Answer · 2010-05-11 18:13:02Z

4

I don't think either method will be much faster than the other, but if you choose a 'datetime' it will be a lot easier to operate with the standard date time functions.

MySQL doesn't support functional indexes.

edited May 11, 2010 at 18:13

answered May 11, 2010 at 17:14

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

whoan · Accepted Answer · 2015-09-11 21:27:38Z

-3

I think using an integer will be faster because where datetime >= '2010-09-30 01:31:41' will never hit an index. However where intTimestamp >= 237492347 will.

edited Sep 11, 2015 at 21:27

whoan

8,6094 gold badges43 silver badges50 bronze badges

answered Nov 12, 2010 at 1:51

socialcentral

1

2 Comments

Tamlyn Over a year ago

Why would that not use an index?

Frankie Over a year ago

It does hit an index (provided the index is there). You can dig into MySQL way of performing queries by using EXPLAIN SELECT (...)

Collectives™ on Stack Overflow

Indexing datetime in MySQL

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related