MySQL Query time optimization

Select COUNT (*) from table where buytime>= ' 2011-5-31 00:00:00 ' and buytime<= ' 2011-6-6 23:59:59 ' GROUP by ItemName

Select COUNT (*) from table where Buytime>=unix_stamp (' 2011-5-31 00:00:00 ') and Buytime<=unix_stamp (' 2011-6-6 23:59:59 ') group by ItemName.

In MySQL, there are swap functions for traditional time functions and Unix timestamps. Unix_timestamp (DateTime) and From_unixtime (Unixtime)

--------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------

Previously said MySQL Time field has date,time,datetime,timestamp, etc., in the actual query time period, we often store the entire field, for example, like 2011-03-10 00:00:00, there are separate storage year, month, Day Way, Or you can use the right () and other characters to intercept the function for query operation. Anyway, we are all going to query for a period of time. I recently in the game props purchase inquiries, as required, we have to follow the way of week, check the sales of props for a week. This is used in the following way:

Select COUNT (*) from table where buytime>= ' 2011-5-31 00:00:00 ' and buytime<= ' 2011-6-6 23:59:59 ' GROUP by ItemName

This is a more traditional way, we need to specify the start and end time, often use >,between,<,= to do time-period planning, but we sent here buytime is not the integer data, efficiency is low, especially after the data reached millions speed obviously down. So we take a second approach to solve:

Unix time stamp

Each time corresponds to a unique UNIX timestamp, time starts from 1970-1-1 00:00:00, every second, plus 1, in MySQL there is a traditional time function and the Unix time stamp interchange function. Unix_timestamp (DateTime) and From_unixtime (Unixtime)

Mysql> Select Unix_timestamp (' 2011-8-2 8:50:34 ');
+------------------------------------+
| Unix_timestamp (' 2011-8-2 8:50:34 ') |
+------------------------------------+
| 1312246234 |
+------------------------------------+
1 row in Set (0.03 sec)

Mysql> Select From_unixtime (1312246234);
+---------------------------+
| From_unixtime (1312246234) |
+---------------------------+
| 2011-08-02 08:50:34 |
+---------------------------+
1 row in Set (0.00 sec)

So pull, we replace the original Time field with the Unix integer field now, by comparing the integers, we can greatly improve the efficiency, when we query, we convert the start end time to the Unix timestamp.

Select COUNT (*) from table where Buytime>=unix_stamp (' 2011-5-31 00:00:00 ') and Buytime<=unix_stamp (' 2011-6-6 23:59:59 ') group by ItemName.

However, in this way, the trouble is that the query will need to flip the time once.

MySQL Query time optimization