Whether the MySQL time field uses INT or DateTime_MySQL

When parsing DEDECMS today, we found that the MYSQL time fields of deder use the INT type, and then we found this article online. it seems that if the time field is involved in the calculation, it would be better to use the int type, first, you do not need to convert the fields during the search, which is directly used for time comparison! Second, the efficiency described below. when parsing DEDECMS today, we found that the MYSQL time fields of deder are all used

'Senddata' int (10) unsigned not null default '0 ';

Later, I found this article on the internet. it seems that if the time field is involved in the calculation, it would be better to use int. in this case, the field conversion operation is not required for retrieval, and it is directly used for time comparison! Second, the efficiency is also higher as described below.

In the final analysis, int is used to replace the data type, which is more efficient.

Environment:

Windows XP PHP Version 5.2.9 MySQL servers 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 let's start the test. The environment is php. Insert 1 million pieces of data into each table first. The number of inserts is divided into 200 times, and 5000 entries are imported into the database each time.

Table 1 execution record: page Running time: 26.5997889042 seconds, an interesting phenomenon found during insertion: SELECT count (id) FROM 'date _ test' WHERE 1 results in 100 w, the direct select * from 'date _ test' is 1,000,374 results. (This is a possible close value, please refer to MySQL FAQ 3.11 ).

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, there are 1,000,224 records.

Table 4 execution record: page Running time: 67.5393900871 seconds. this time, there are 1,000,073 records.

Now, the start_time fields of the four tables are indexed one by one.

Test the update of the four 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 run time: 2.45077300072 seconds (varchar, datetime time)

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

Test the reading of the four tables, select 100 random records respectively, query with the primary key id as the condition, and record the time:

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 run time: 0.334048032761 seconds (varchar, datetime time)

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

Test the reading of four tables, select 10 random records respectively, query with star_time as the condition, and record the time:

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 run time: 39.7210869789 seconds (varchar, datetime time)

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

Because the amount is small, it makes sense for us to make small changes by default.

Conclusion:

If a large number of queries such as select * from table where time> XX exist in large data volumes, it is meaningful to use int for datetime in MySQL5.1.