MySQL time field using int or datetime description _php Tips

Today, when parsing dedecms found Deder mysql Time field, are used

' SendData ' int (a) unsigned not NULL DEFAULT ' 0 ';

Then find this article on the Internet, it seems that if the time field has participated in the operation, with an int better, a search without the conversion in the field operation, directly used for time comparison! Secondly, the efficiency is also higher than that described below.

In the final analysis: it is more efficient to replace the data type with int.

Environment:

Windows XP PHP Version 5.2.9 MySQL Server 5.1

First step, create a table date_test (not 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 two, create a 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 three, create a 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 fourth, create 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 start to do the test, the environment is PHP, first insert 1 million pieces of data into each table. The insertion time is divided 200 times, each time enters the storehouse 5,000.

Table One Execution record: page run time: 26.5997889042 seconds, an interesting phenomenon was found when inserting: SELECT COUNT (id) from ' date_test ' WHERE 1 results in 100w and direct select * from ' Date_ Test ' is a 1,000,374-piece result. (See the MySQL FAQ 3.11 for a potentially close value later.)

Table II Execution record: page run time: 62.3908278942 seconds, this record is 1,000,066.

Table three execution record: page run time: 30.2576560974 seconds, this one is 1,000,224.

Table four Execution record: page run time: 67.5393900871 seconds, this time is: 1,000,073.

Now add the Start_time field one by one of the four tables to the index.

Test the updates for four tables, update 100 records separately, and record the time:

Table A: Page run time: 2.62180089951 seconds (not fixed length, int time)

Table two: page run time: 2.5475358963 seconds (fixed length, int time)

Table Three: Page run time: 2.45077300072 seconds (varchar,datetime time)

Table four: Page run time: 2.82798409462 seconds (char,datetime time)

Test the reading of four tables, select 100 random Records, query by primary key ID, and record the time:

Table A: Page run time: 0.382651090622 seconds (not fixed length, int time)

Table two: page run time: 0.542181015015 seconds (fixed length, int time)

Table Three: Page run time: 0.334048032761 seconds (varchar,datetime time)

Table four: Page run time: 0.506206989288 seconds (char,datetime time)

Test the reading of four tables, select 10 random records separately, star_time as criteria, and record the time:

Table A: Page run time: 30.1972880363 seconds (not fixed length, int time)

Table two: page run time: 65.1926910877 seconds (fixed length, int time)

Table Three: Page run time: 39.7210869789 seconds (varchar,datetime time)

Table four: Page run time: 70.4632740021 seconds (char,datetime time)

Because the volume is small, so we default even small changes, it makes sense.

Conclusion:

Large data volumes, if there is a large number of select * from table where time >xx such queries, it makes sense to use int for datetime at MySQL5.1.