Explanation of the problem of selecting MySQL storage time type

Explanation of the problem of selecting MySQL storage time type

The datetime type is usually used to store time in MySQL, but many systems now also use int to store unix timestamps. What is the difference between them? My summary is as follows:

int

(1) 4 bytes of storage. The length of INT is 4 bytes, which takes up less storage space than datatime. The storage space of int index is also relatively small, and the sorting and query efficiency is relatively high.

(2) The readability is extremely poor and the data cannot be seen intuitively

TIMESTAMP

(1) 4 bytes storage

(2) Values ​​are saved in UTC format

(3) Time zone conversion: convert to the current time zone when storing and convert back to the current time zone when retrieving.

(4) TIMESTAMP values ​​cannot be earlier than 1970 or later than 2037

datetime

(1) 8 bytes of storage

(2) Not related to time zone

(3) Retrieve and display DATETIME values ​​in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'

As MySQL performance gets higher and higher, I think the storage method of time depends on personal habits and project requirements.

Share two articles about int vs timestamp vs datetime performance testing

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;

Model 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

Execute 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)

MySQL DATETIME vs TIMESTAMP vs INT performance and benchmarking with InnoDB

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links

You may also be interested in:
  • MySQL time type selection
  • How to choose the right MySQL datetime type to store your time
  • About mysql time type selection
  • Parsing MySql and Java time types
  • Summary of MySQL date data type and time type usage
  • MySQL time types and modes details

<<:  JavaScript implements simple scroll window

>>:  Detailed explanation of the solution to the failure of VMware to open the module diskearly

Recommend

JavaScript css3 to implement simple video barrage function

This article attempts to write a demo to simulate...

Implementation of CSS text shadow gradually blurring effect

text-shadow Add a shadow to the text. You can add...

JavaScript Array Methods - Systematic Summary and Detailed Explanation

Table of contents Common array methods Adding and...

Xhtml special characters collection

nbsp &#160; no-break space = non-breaking spa...

Introduction to Semantic XHTML Tags

The first point to make is that people can judge t...

Nofollow makes the links in comments and messages really work

Comments and messages were originally a great way...

Solution to the error reported by Mysql systemctl start mysqld

Error message: Job for mysqld.service failed beca...

Installation and use tutorial of Elasticsearch tool cerebro

Cerebro is an evolution of the Elasticsearch Kopf...

WeChat applet custom tabbar component

This article shares the specific code of the WeCh...

Summary of MySQL date and time functions (MySQL 5.X)

1. MySQL gets the current date and time function ...

In-depth explanation of Set and WeakSet collections in ES6

Table of contents Set is a special collection who...

What is ssh port forwarding? What's the use?

Table of contents Preface 1. Local port forwardin...

JavaScript BOM location object + navigator object + history object

Table of contents 1. Location Object 1. URL 2. Pr...