Whether the MySQL time field uses int or datetime

Today, when parsing dedecms, the MySQL time field found in Deder is used

int (notNULLDEFAULT'0';

Then found this article on the Internet, it seems that if the time field has to participate in the operation, with int better, one does not have to convert the operation on the field, directly for the time comparison! Secondly, the efficiency is higher as described below.

In the final analysis: using int instead of the data type is more efficient.

Environment:

5.2. 9  5.1

First step, create a table date_test (non-fixed length, int time)

CREATE TABLE  intnotnull int not  null  VARCHAR255notNULLPRIMARYKEY   = InnoDB;

Second step, create a second table date_test2 (fixed length, int time)

 create  table  not  null   auto_increment, ' start_time '  int  not  null  , ' some_content '  char  (255 ) not  null   primary  key   =  InnoDB; 

Step three, create the third table date_test3 (varchar, datetime time)

 create  table  not  null   auto_increment, ' start_time '  datetime  not  null   varchar  ( Span style= "color: #800000; Font-weight:bold; " >255 ) not  null  ,  primary  key   (' id ')) ENGINE  =  InnoDB; 

Fourth step, create fourth Table Date_test3 (char, datetime time)

 create  table  not  null   auto_increment, ' start_time '  datetime  not  null   char  ( Span style= "color: #800000; Font-weight:bold; " >255 ) not  null  ,  primary  key   (' id ')) ENGINE  =  InnoDB; 

OK, now let's start testing, the environment is PHP, first insert 1 million data to each table. Insert the time divided 200 times, each into the library 5,000.

Table One Execution record: page run time: 26.5997889042 seconds, insert the time to find an interesting phenomenon: select COUNT (id) from ' date_test ' WHERE 1 result is 100w, and direct SELECT * from ' Date_ Test ' is 1,000,374 results. (See MySQL FAQ 3.11 for a possible approximate value later).

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

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

Table four execution record: page running time: 67.5393900871 seconds, this time is: 1,000,073.

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

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

Table One: Page run time: 2.62180089951 seconds (non-fixed length, int time)

Table II: 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 separately, query with the primary key ID, and record the time:

Table One: Page run time: 0.382651090622 seconds (non-fixed length, int time)

Table II: 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 the four tables, select 10 random records separately, star_time as the condition query, and record the time:

Table One: Page run time: 30.1972880363 seconds (non-fixed length, int time)

Table II: 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 smaller, it makes sense for us to default even small changes.

Conclusion:

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

Whether the MySQL time field uses int or datetime