This article has been proved by my practice

In database design, we often need to design the time field. In MYSQL, the time field can use int, TIMESTAMP, datetime three types to store, so which of these three types is used to store time with high performance and good efficiency? Piao yi on this problem, to a practice out of real knowledge.

MYSQL version: 5.5.19

Establish a table:

CREATE TABLE IF NOT EXISTS `datetime_test` (
  `id` int(11) NOT NULL,
  `d_int` int(11) NOT NULL DEFAULT '0',
  `d_timestamp` timestamp NULL DEFAULT NULL,
  `d_datetime` datetime DEFAULT NULL
) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;
ALTER TABLE `datetime_test`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;
Copy the code

Insert 1 million test data:

<? php header( 'Content-Type: text/html; charset=UTF-8' ); set_time_limit(300); $pdo = new pdo ("mysql:host=localhost; dbname=test","root","123"); for ($i = 1; $i <= 1000000; $i++) { $d_int=$i; $pdo->exec("insert into datetime_test(d_int,d_timestamp,d_datetime) values($d_int,FROM_UNIXTIME($d_int),FROM_UNIXTIME($d_int))"); }Copy the code

Take the middle 200,000 items for query test:

SELECT FROM_UNIXTIME(400000), FROM_UNIXTIME(600000)
1970-01-05 23:06:40, 1970-01-08 06:40:00
Copy the code

In the first case, MyISAM engine, d_int/d_timestamp/d_datetime are not indexed

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_int >400000 AND d_int<600000
Copy the code

Query takes 0.0780 seconds

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_int>UNIX_TIMESTAMP('1970-01-05 23:06:40') AND d_int<UNIX_TIMESTAMP('1970-01-08 06:40:00')
Copy the code

Query cost 0.0780 seconds efficient.

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_timestamp>'1970-01-05 23:06:40' AND d_timestamp<'1970-01-08 06:40:00 'Copy the code

The query took 0.4368 seconds

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE UNIX_TIMESTAMP(d_timestamp)>400000 AND UNIX_TIMESTAMP(d_timestamp)<600000
Copy the code

For timestamp type, the query efficiency is very high using UNIX_TIMESTAMP built-in function, almost equal to int; Direct comparisons with dates are inefficient.

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_datetime>'1970-01-05 23:06:40' AND d_datetime<'1970-01-08 06:40:00 'Copy the code

The query took 0.1370 seconds

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE UNIX_TIMESTAMP(d_datetime)>400000 AND UNIX_TIMESTAMP(d_datetime)<600000
Copy the code

For datetime type, using the UNIX_TIMESTAMP built-in function is inefficient and is not recommended. Direct and date comparison, efficiency is ok.

In MyISAM, d_int/d_timestamp/d_datetime are indexed

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_int >400000 AND d_int<600000
Copy the code

The query took 0.3900 seconds

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_int>UNIX_TIMESTAMP('1970-01-05 23:06:40') AND d_int<UNIX_TIMESTAMP('1970-01-08 06:40:00')
Copy the code

Query cost 0.3824 seconds for int type, index efficiency is lower, float easy guess is due to the design of the table structure, more index, but more index lookup.

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_timestamp>'1970-01-05 23:06:40' AND d_timestamp<'1970-01-08 06:40:00 'Copy the code

The query takes 0.5696 seconds

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE UNIX_TIMESTAMP(d_timestamp)>400000 AND UNIX_TIMESTAMP(d_timestamp)<600000
Copy the code

For timestamp types, there seems to be little difference between index and no index.

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_datetime>'1970-01-05 23:06:40' AND d_datetime<'1970-01-08 06:40:00 'Copy the code

Query takes 0.4508 seconds

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE UNIX_TIMESTAMP(d_datetime)>400000 AND UNIX_TIMESTAMP(d_datetime)<600000
Copy the code

For datetime types, having an index is inefficient.

In the third case, InnoDB engine, d_int/d_timestamp/d_datetime are not indexed

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_int >400000 AND d_int<600000
Copy the code

The query took 0.3198 seconds

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_int>UNIX_TIMESTAMP('1970-01-05 23:06:40') AND d_int<UNIX_TIMESTAMP('1970-01-08 06:40:00')
Copy the code

InnoDB’s query efficiency is three times lower than MyISAM’s.

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_timestamp>'1970-01-05 23:06:40' AND d_timestamp<'1970-01-08 06:40:00 'Copy the code

The query took 0.7092 seconds

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE UNIX_TIMESTAMP(d_timestamp)>400000 AND UNIX_TIMESTAMP(d_timestamp)<600000
Copy the code

For the timestamp type, the query efficiency using the UNIX_TIMESTAMP built-in function is also higher than that of direct and date comparison.

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_datetime>'1970-01-05 23:06:40' AND d_datetime<'1970-01-08 06:40:00 'Copy the code

The query took 0.3834 seconds

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE UNIX_TIMESTAMP(d_datetime)>400000 AND UNIX_TIMESTAMP(d_datetime)<600000
Copy the code

The query takes 0.9794 seconds. For datetime type, direct comparison with date is more efficient than query with UNIX_TIMESTAMP built-in function.

In the fourth case, InnoDB engine, the three fields d_int/d_timestamp/d_datetime have indexes

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_int >400000 AND d_int<600000
Copy the code

Query takes 0.0522 seconds

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_int>UNIX_TIMESTAMP('1970-01-05 23:06:40') AND d_int<UNIX_TIMESTAMP('1970-01-08 06:40:00')
Copy the code

InnoDB engine performance is much better than MyISAM with indexes.

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_timestamp>'1970-01-05 23:06:40' AND d_timestamp<'1970-01-08 06:40:00 'Copy the code

The query took 0.1776 seconds

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE UNIX_TIMESTAMP(d_timestamp)>400000 AND UNIX_TIMESTAMP(d_timestamp)<600000
Copy the code

MYSQL > select * from UNIX_TIMESTAMP; select * from UNIX_TIMESTAMP;

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_datetime>'1970-01-05 23:06:40' AND d_datetime<'1970-01-08 06:40:00 'Copy the code

The query took 0.0820 seconds

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE UNIX_TIMESTAMP(d_datetime)>400000 AND UNIX_TIMESTAMP(d_datetime)<600000
Copy the code

For datetime types, it is not recommended to use UNIX_TIMESTAMP as an index.

【 Summary 】 :

For MyISAM, without indexing (recommended), efficiency from high to low: Int >UNIX_TIMESTAMP(timestamp) > datetime (timestamp) >UNIX_TIMESTAMP(datetime)

For the MyISAM engine, in the case of indexing, the efficiency is from high to low: UNIX_TIMESTAMP(timestamp) > int > datetime (timestamp) >UNIX_TIMESTAMP(datetime).

For The InnoDB engine, without indexes (not recommended), efficiency from high to low: Int > UNIX_TIMESTAMP(timestamp) > datetime (timestamp) > UNIX_TIMESTAMP(datetime)

For the InnoDB engine, the efficiency of indexing is from high to low: Int > datetime > timestamp > UNIX_TIMESTAMP(timestamp) > UNIX_TIMESTAMP(datetime).

In a word, for MyISAM, UNIX_TIMESTAMP(timestamp) is used for comparison; For the InnoDB engine, index using int or datetime direct time comparison.

Source: reprinted blog.csdn.net/zmzwll1314/…

Thanks for the hard work of the author.