CODEWITHSUNDEEP
mysqlperformancedatetimetimestamp
Kot Szutnik
Kot Szutnik

Reputation: 105

Timestamp vs datetime vs int - with and without indexes - tests

In my case I would only test, which date time format is optimal for my query duration. Here's my query:

   SELECT 
    max(ColumnA), -- bigint
    CreateDate, 
    ColumnB, 
    EndTime, 
    ColumnC, 
    ColumnD, 
    ColumnE, 
    ColumnF, -- int
    StartTime, -- timestamp or datetime or int - UNIX_TIMESTAMP(StartTime)
    ColumnG, 
    ColumnH, 
    ColumnI, 
    ColumnJ, 
    UpdateDate, 
    ColumnK FROM TABLE

Conditions:

-- with indexes
  (Iteration 1) WHERE StartTime BETWEEN "2013-01-18 16:50:00" AND "2013-10-18 18:05:00" AND ColumnF in(5428) GROUP BY ColumnF,StartTime
  (Iteration 2) WHERE StartTime BETWEEN "2013-05-18 11:00:00" AND "2013-06-23 22:05:00" AND ColumnF in(5428) GROUP BY ColumnF,StartTime
  (Iteration 3) WHERE StartTime BETWEEN "2013-08-18 11:00:00" AND "2013-08-23 22:05:00" AND ColumnF in(7752) GROUP BY ColumnF,StartTime
  (Iteration 4) WHERE StartTime BETWEEN "2013-01-18 16:50:00" AND "2013-10-18 18:05:00" AND ColumnF in(5428,5675,444) GROUP BY ColumnF,StartTime
  (Iteration 5) WHERE StartTime BETWEEN "2013-09-01 16:50:00" AND "2013-09-15 18:05:00" AND ColumnF in(5428,5675,444) GROUP BY ColumnF,StartTime
-- and same without indexes

Tests info:

Count = 400K
Engine version = 5.6.10
DBEngine = InnoDB
Tests procedure - MySQL restart before any query.

Results:

With index on StartTime, with index on ColumnF                      

    Iteration   1      2       3       4       5
timestamp   0.094   0.094   0.124   0.124   0.125
datetime    0.125   0.109   0.141   0.156   0.156
int         0.125   0.124   0.094   0.156   0.156
Rows            38     8       1       128  8

Without index on StartTime, with index on ColumnF                   
    Iteration   1      2       3       4      5
timestamp   0.078   0.062   0.062   0.109   0.125
datetime    0.078   0.078   0.078   0.140   0.125
int         0.078   0.078   0.078   0.140   0.125
Rows            38     8       1       128      8

So I decided to use Timestamps without indexes (but without indexes results look very similar).

EDIT: I really don't know why I decided to use only one query. Maybe it's laziness, maybe I shouldn't work on sundays:) Now tests are irrelevant and conclusion are false. I rewrite all tests informations, when i make my tests... normaly:)

EDIT2: FIXED

Upvotes: 4

Views: 8174

Answers (1)

artragis
artragis

Reputation: 3713

One day I asked a question about when we chose Datetime over Timestamp. What I think is that you will not earn lot of time with integer timestamp. Also, remember that TIMESTAMP and DATETIME are aliases in MySQL, if you use PostgreSQL, you will only have TIMESTAMP. So here, your test seems to be irrelevent.

EDIT : they are not equivalent.they are just formated the same way. As a matter of fact Timestamp is just a timezone insentitive type. It alows you to get rid of this matter. But it brings you to less customization and is restricted from 1970 to 2038. If you have other dates to store (earlier or later) you will fail.

Two things make me say that :

  • two few iterations
  • no dispertion metric
  • almost no difference in everything you are testing

The big difference between INT and TIMESTAMP is the place in memory. You do not test it.and the time span observed is really short.

I think you guess I'm totaly in favour of DateTime over integer.

Upvotes: 2

Related Questions