Description of whether the MySQL time field uses int or datetime _php tutorial

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

' SendData ' int (ten) unsigned not NULL DEFAULT ' 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:

Windows XP PHP Version 5.2.9 MySQL Server 5.1

First step, 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) is not NULL, PRIMARY KEY (' id ') ) ENGINE = InnoDB;

Second step, 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) is 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) is not NULL, PRIMARY KEY (' id ') ) ENGINE = InnoDB;

Fourth step, 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) is 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.

http://www.bkjia.com/PHPjc/325104.html www.bkjia.com true http://www.bkjia.com/PHPjc/325104.html techarticle today parsing dedecms found Deder mysql Time field, all with ' senddata ' int (ten) unsigned not NULL DEFAULT ' 0 ', and then found this article on the Internet, it seems that if the time field has ...

