Test whether to use INT or DateTime for MySQL query time period

During MySQL query time period, it will be more efficient to select INT, or will it be more efficient to select DateTime? To solve this problem, we will perform a test to see whether INT or DateTime should be used in the MySQL query period.

Environment:

Windows XP
PHP Version 5.2.9
MySQL servers 5.1

Step 1: Create a table date_test with a non-fixed length and an 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 with a fixed length and 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_test3varchar and 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_test3char and 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. Later, we can see that 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 (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 s 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 (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 s 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 (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 s 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.
 

MySQL foreign key and reference integrity

Seven considerations for converting MySQL to Oracle

New Pricing strategies for MySQL database products

Provides you with an in-depth understanding of MYSQL string connection

Implementation of MySQL + Apache cluster mode