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

MySql 8.0.11 installation and configuration tutorial

Official website address: https://dev.mysql.com/d...

Installation tutorial of mysql8.0rpm on centos7

First, download the diagram 1. First uninstall th...

HTML uses marquee to achieve text scrolling left and right

Copy code The code is as follows: <BODY> //...

JavaScript data visualization: ECharts map making

Table of contents Overview Precautions 1. Usage 2...

A brief discussion on the performance issues of MySQL paging limit

MySQL paging queries are usually implemented thro...

How to forget the root password in Mysql8.0.13 under Windows 10 system

1. First stop the mysql service As an administrat...

How to configure anti-hotlinking for nginx website service (recommended)

1. Principle of Hotlinking 1.1 Web page preparati...

Solve the problem of OpenLayers 3 loading vector map source

1. Vector Map Vector graphics use straight lines ...

MySQL data loss troubleshooting case

Table of contents Preface On-site investigation C...

Examples of importing and exporting MySQL table data

This article describes the import and export oper...

Perfect Solution for No rc.local File in Linux

Newer Linux distributions no longer have the rc.l...

Create a screen recording function with JS

OBS studio is cool, but JavaScript is cooler. Now...

mysql startup failure problem and scenario analysis

1. One-stop solution 1. Problem analysis and loca...

Mysql join query principle knowledge points

Mysql join query 1. Basic concepts Connect each r...