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

Question

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

Kot, can you try with `ColumnF = 5428` see if it changes anything? What version of MySQL is it? — जलजनक, Feb 17 '13 at 13:49
@DoSparKot Of course I can try if You need it. Just give me more time:) — Kot Szutnik, Feb 17 '13 at 14:30

score 2 · Answer 1 · edited May 23 '17 at 11:46

2

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.

edited May 23 '17 at 11:46

Community

1
1

answered Feb 17 '13 at 13:12

artragis

3,677
1
18
30

2

`DATETIME supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' and TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.` except for `MAXDB` SQL mode. – जलजनक Feb 17 '13 at 13:25
edited. sorry for this error, I misread the documentation and relied on the format they are both displayed . – artragis Feb 17 '13 at 13:53
Thx for fast answer. In my case I would only test, which date time format is optimal for my query duration (maybe should I write it earlier). In that case (I know that I tested very similar date types) Timestamp without indexes is faster than any other types - It's interesting as I think. So - in this case - I should use Timestamp (if timestamp range is not to small) column without indexes - this is my conclusion. – Kot Szutnik Feb 17 '13 at 14:02
it is not really faster. Moreover you just don't iterate your test enough time to get a real data. When you bench something you are doing statistics. You cannot deduce something from only one set of data. – artragis Feb 17 '13 at 14:04
@artragis You're absolutely right and 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. Thanks! – Kot Szutnik Feb 17 '13 at 14:24

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

1 Answers1