Shulou(Shulou.com)05/31 Report--

This article mainly explains "MySQL query period with INT or DateTime is better", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "MySQL query time period with INT or DateTime is better"!

Whether to use INT or DateTime for MySQL query period

To solve this problem, let's do a test to see whether the MySQL query period should use INT or DateTime.

Environment:

WindowsXP

PHPVersion5.2.9

MySQLServer5.1

The first step is to create a table date_test (variable length, int time)

CREATETABLE`test`.`date _ test` (

`id`INTNOTNULLAUTO _ INCREMENT

`start_ time`INTNOTNULL

`VARCHAR some_ content`VARCHAR (255) NOTNULL

PRIMARYKEY (`id`)

) ENGINE=InnoDB

The second step, create the second table date_test2 (fixed length, int time)

CREATETABLE`test`.`date _ test2` (

`id`INTNOTNULLAUTO _ INCREMENT

`start_ time`INTNOTNULL

`The some_ content`Char (255) NOTNULL

PRIMARYKEY (`id`)

) ENGINE=InnoDB

The third step, create the third table date_test3 (varchar, datetime time)

CREATETABLE`test`.`date _ test3` (

`id`INTNOTNULLAUTO _ INCREMENT

`DATETIMENOTNULL `DATETIMENOTNULL

`VARCHAR some_ content`VARCHAR (255) NOTNULL

PRIMARYKEY (`id`)

) ENGINE=InnoDB

Step 4, create the fourth table date_test3 (char, datetime time)

CREATETABLE`test`.`date _ test4` (

`id`INTNOTNULLAUTO _ INCREMENT

`DATETIMENOTNULL `DATETIMENOTNULL

`The some_ content`Char (255) NOTNULL

PRIMARYKEY (`id`)

) ENGINE=InnoDB

Ok, now let's start testing in php, inserting 1 million pieces of data into each table first. When inserting, it is divided into 200 times, and 5000 items are entered into the library each time.

Whether to use INT or DateTime for MySQL query period

Table 1 execution record: page running time: 26.5997889042 seconds. An interesting phenomenon was found when inserting: the result of SELECTcount (id) From`date _ test`WhERE1 is 100w, while the direct select* from`date _ test` is 1000374 results. (see MySQLFAQ3.11 when you see later that this is a possible close value.)

Table 2 execution record: page run time: 62.3908278942 seconds, this record is 1000066.

Table 3 execution record: page run time: 30.2576560974 seconds, this time is 1000224.

Table 4 execution record: page run time: 67.5393900871 seconds, this time: 1000073.

Now index the start_time fields of each of the four tables.

Test the updates of the four tables, update100 the records separately, and record the time:

Table 1: page run time: 2.62180089951 seconds (variable length, int time)

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

Table 3: page run time: 2.45077300072 seconds (varchar,datetime time)

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

Test the reading of four tables, select100 random records respectively, query with the primary key id, and record the time:

Table 1: page run time: 0.382651090622 seconds (variable length, int time)

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

Table 3: page run time: 0.334048032761 seconds (varchar,datetime time)

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

Test the reading of four tables, select10 random records respectively, query under the condition of star_time, and record time:

Table 1: page run time: 30.1972880363 seconds (variable length, int time)

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

Table 3: page run time: 39.7210869789 seconds (varchar,datetime time)

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

Because the quantity is relatively small, we acquiesce that even small changes are meaningful.

Conclusion:

Under a large amount of data, if there are a large number of queries such as select*fromtablewhere time > XX, it makes sense to use int for datetime in MySQL5.1.

At this point, I believe that everyone on the "MySQL query period with INT or DateTime is better" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.