A description of whether the MySQL time field USES INT or DateTime

Today, when parsing DEDECMS, we found that the MYSQL time field of deder is used

`senddata` int(10) unsigned NOT NULL DEFAULT '0';

Then I found this article on the Internet. It seems that if the time field is involved in the calculation, int is better. When 1 is used for retrieval, it is not necessary to convert the calculation on the field, but directly used for time comparison! It is also more efficient as described below.

The bottom line: it's more efficient to replace the data type with int.

Environment:

Windows XP
PHP Version 5.2.9
MySQL Server 5.1

Step 1. Create a table date_test (non-fixed length, int time)

CREATE TABLE `test`.`date_test` (
`id` INT NOT NULL AUTO_INCREMENT ,
`start_time` INT NOT NULL ,
`some_content` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;

Step 2. Create the second table date_test2 (fixed length, int time)

CREATE TABLE `test`.`date_test2` (
`id` INT NOT NULL AUTO_INCREMENT ,
`start_time` INT NOT NULL ,
`some_content` CHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;

Step 3. Create the third table date_test3 (varchar, datetime time)

CREATE TABLE `test`.`date_test3` (
`id` INT NOT NULL AUTO_INCREMENT ,
`start_time` DATETIME NOT NULL ,
`some_content` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;

Step 4. Create the fourth table date_test3 (char, datetime time)

CREATE TABLE `test`.`date_test4` (
`id` INT NOT NULL AUTO_INCREMENT ,
`start_time` DATETIME NOT NULL ,
`some_content` CHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;

ok, now we're going to do the test, php, and we're going to insert 1 million pieces of data into each table. The insertion time is divided into 200 times, each time into the warehouse 5000 pieces.

Table 1 execution record: page running time: 26.5997889042 seconds. When inserting, an interesting phenomenon is found: SELECT count(id) FROM 'date_test' WHERE 1 results in 100w, while direct select * from 'date_test' results in 1,000,374. (see MySQL FAQ 3.11 for a possible approximate value.)

Table 2 execution records: page running time: 62.3908278942 seconds, this record is 1,000,066.

Table 3 execution records: page running time: 30.2576560974 seconds, this time 1,000,224.

Table 4 execution records: page running time: 67.5393900871 seconds, this time: 1,000,073.

Now index the start_time field 11 of the four tables.

Test the update of 4 tables, update 100 records respectively, and record the time:

Table 1: page running time: 2.62180089951 seconds (non-fixed length, int time)

Table 2: page running time: 2.5475358963 seconds (fixed length, int time)

Table 3: page running time: 2.45077300072 seconds (varchar,datetime time)

Table 4: page running time: 2.82798409462 seconds (char,datetime time)

The reading of 4 tables was tested, and 100 random records were select respectively. The primary key id was used as a conditional query, and the time was recorded:

Table 1: page running time: 0.382651090622 seconds (non-fixed length, int time)

Table 2: page running time: 0.542181015015 seconds (fixed length, int time)

Table 3: page running time: 0.334048032761 seconds (varchar,datetime time)

Table 4: page running time: 0.506206989288 seconds (char,datetime time)

The reading of 4 tables was tested, and 10 random records were select respectively. The query was conditional on star_time, and the time was recorded as follows:

Table 1: page running time: 30.1972880363 seconds (non-fixed length, int time)

Table 2: page running time: 65.1926910877 seconds (fixed length, int time)

Table 3: page running time: 39.7210869789 seconds (varchar,datetime time)

Table 4: page running time: 70.4632740021 seconds (char,datetime time)

Because the quantity is small, we assume that even small changes make sense.

Conclusion:

Large data volume, if there is a large amount of select * from table where time > For queries like XX, it makes sense to swap int for datetime when MySQL 5.1 is used.