Re: Timestamp data type / indexing and seacrhing
Posted by: Rick James
Date: October 22, 2014 03:44PM

Before version 5.6.4, TIMESTAMP was stored as a 4-byte integer, representing the number of seconds into the epoch, based on GMT. It is converted as you read/write it into the local timezone.

Starting with 5.6.4, TIMESTAMP can have "fractional" values; it can have up to 6 decimal places. Without zero decimal places, I think it now takes 5 bytes.

Any INT is stored as 4 bytes.

The processing speed between INT and TIMESTAMP, as Peter says, is insignificant. The real question is what do you need.

It is usually impractical to have a time (be it INT or TIMESTAMP or DATETIME) as the PRIMARY KEY. It is reasonable to have a non-UNIQUE index for such, or to have a compound PRIMARY KEY starting with some form of time.

I bring up PRIMARY KEY because doing a "range" lookup based on the PRIMARY KEY is faster (in InnoDB) than using a "secondary key". Please provide SHOW CREATE TABLE so we can discuss this (and other) topics in more detail.

> speed of query results in highly important

The number one thing controlling speed is whether I/O needs to be performed. Please also provide SHOW VARIABLES LIKE '%buffer%';

The number two thing is whether an index can be used. So, also provide
EXPLAIN SELECT ...
for the query you want to be fast.

Other things (such as INT vs TIMESTAMP) are relatively insignificant in the performance arena.

Options: ReplyQuote


Subject
Written By
Posted
Re: Timestamp data type / indexing and seacrhing
October 22, 2014 03:44PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.