How to select MySQL storage time type

Time:2020-3-3

The storage time in MySQL usually uses datetime type, but now many systems also use int to store UNIX timestamps. What’s the difference between them? I summarize as follows:

int

(1) 4-byte storage, the length of int is 4 bytes, the storage space is less than DataTime, the storage space of int index is relatively small, and the efficiency of sorting and query is relatively high

(2) Poor readability, unable to see the data intuitively

TIMESTAMP

(1) 4-byte storage

(2) Value is saved in UTC

(3) Time zone conversion: the current time zone is converted during storage and then converted back to the current time zone during retrieval.

(4) Timestamp value cannot be earlier than 1970 or later than 2037

datetime

(1) 8-byte storage

(2) Time zone independent

(3) Retrieve and display datetime values in the format ‘yyyy-mm-dd HH: mm: Ss’. The supported range is’ 1000-01-01 00:00:00 ‘to’ 9999-12-31 23:59:59 ‘

With the increasing performance of MySQL, I think the storage mode of time depends on my habits and project requirements

Share two articles about int vs timestamp vs datetime performance test

MyISAM: MySQL datetime vs timestamp vs int tester


CREATE TABLE `test_datetime` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`datetime` FIELDTYPE NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;

Type configuration

  • kip-locking
  • key_buffer = 128M
  • max_allowed_packet = 1M
  • table_cache = 512
  • sort_buffer_size = 2M
  • read_buffer_size = 2M
  • read_rnd_buffer_size = 8M
  • myisam_sort_buffer_size = 8M
  • thread_cache_size = 8
  • query_cache_type = 0
  • query_cache_size = 0
  • thread_concurrency = 4

test

DATETIME   14111 14010        14369     130000000
TIMESTAMP  13888        13887        14122     90000000
INT        13270        12970        13496     90000000

Implementation of MySQL


mysql> select * from test_datetime into outfile ‘/tmp/test_datetime.sql';
Query OK, 10000000 rows affected (6.19 sec)

mysql> select * from test_timestamp into outfile ‘/tmp/test_timestamp.sql';
Query OK, 10000000 rows affected (8.75 sec)

mysql> select * from test_int into outfile ‘/tmp/test_int.sql';
Query OK, 10000000 rows affected (4.29 sec)

alter table test_datetime rename test_int;
alter table test_int add column datetimeint INT NOT NULL;
update test_int set datetimeint = UNIX_TIMESTAMP(datetime);
alter table test_int drop column datetime;
alter table test_int change column datetimeint datetime int not null;
select * from test_int into outfile ‘/tmp/test_int2.sql';
drop table test_int;

So now I have exactly the same timestamps from the DATETIME test, and it will be possible to reuse the originals for TIMESTAMP tests as well.

mysql> load data infile ‘/export/home/ntavares/test_datetime.sql’ into table test_datetime;
Query OK, 10000000 rows affected (41.52 sec)
Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0

mysql> load data infile ‘/export/home/ntavares/test_datetime.sql’ into table test_timestamp;
Query OK, 10000000 rows affected, 44 warnings (48.32 sec)
Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 44

mysql> load data infile ‘/export/home/ntavares/test_int2.sql’ into table test_int;
Query OK, 10000000 rows affected (37.73 sec)
Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0

As expected, since INT is simply stored as is while the others have to be recalculated. Notice how TIMESTAMP still performs worse, even though uses half of DATETIME storage size.

Let’s check the performance of full table scan:


mysql> SELECT SQL_NO_CACHE count(id) FROM test_datetime WHERE datetime > ‘1970-01-01 01:30:00′ AND datetime < ‘1970-01-01 01:35:00′;
+———–+
| count(id) |
+———–+
|  211991 |
+———–+
1 row in set (3.93 sec)

mysql> SELECT SQL_NO_CACHE count(id) FROM test_timestamp WHERE datetime > ‘1970-01-01 01:30:00′ AND datetime < ‘1970-01-01 01:35:00′;
+———–+
| count(id) |
+———–+
|  211991 |
+———–+
1 row in set (9.87 sec)

mysql> SELECT SQL_NO_CACHE count(id) FROM test_int WHERE datetime > UNIX_TIMESTAMP('1970-01-01 01:30:00′) AND datetime < UNIX_TIMESTAMP('1970-01-01 01:35:00′);
+———–+
| count(id) |
+———–+
|  211991 |
+———–+
1 row in set (15.12 sec)

Then again, TIMESTAMP performs worse and the recalculations seemed to impact, so the next good thing to test seemed to be without those recalculations: find the equivalents of those UNIX_TIMESTAMP() values, and use them instead:


mysql> select UNIX_TIMESTAMP('1970-01-01 01:30:00′) AS lower, UNIX_TIMESTAMP('1970-01-01 01:35:00′) AS bigger;
+——-+——–+
| lower | bigger |
+——-+——–+
| 1800 |  2100 |
+——-+——–+
1 row in set (0.00 sec)

mysql> SELECT SQL_NO_CACHE count(id) FROM test_int WHERE datetime > 1800 AND datetime < 2100;
+———–+
| count(id) |
+———–+
|  211991 |
+———–+
1 row in set (1.94 sec)

Innodb:MySQL DATETIME vs TIMESTAMP vs INT performance and benchmarking with InnoDB

summary

The above is the whole content of this article. I hope that the content of this article has some reference learning value for your study or work. Thank you for your support for developepaer. If you want to know more about it, please check the relevant links below

Recommended Today

A detailed explanation of the differences between Perl and strawberry Perl and ActivePerl

Perl is the abbreviation of practical extraction and report language “practical report extraction language”. Application of activestateperl and strawberry PERL on Windows platformcompiler。 Perl   The relationship between the latter two is that C language and Linux system have their own GCC. The biggest difference between activestate Perl and strawberry Perl is that strawberry Perl […]