Iposdb Database Date Function optimization

The following 4 SQL, which is obviously a 2-class SQL statement. May 19, 2017

Question: Walked two times the full table.
Select COUNT (*) active merchant number from (select Merchant_code,count (DISTINCT concat (Merchant_code,date (create_date))) Merchantcodecreatedate from Pos_order where date (create_date) >= ' 2017-05-12 ' and DATE (create_date) <= ' 2017-05-18 ' and trade_status= ' 2 ' GROUP by Merchant_code have merchantcodecreatedate>=4) as tmptable;

650) this.width=650; "title=" 1.png "src=" https://s5.51cto.com/wyfs02/M01/99/52/wKiom1lHRjTAry68AABBj2ot7kk524.png "alt=" Wkiom1lhrjtary68aabbj2ot7kk524.png "/>select count (*) Active tool count from (select Machine_sn,count (DISTINCT concat ( Machine_sn,date (create_date)) Machinesncreatedate from Pos_order where date (create_date) >= ' 2017-05-12 ' and DATE ( create_date) <= ' 2017-05-18 ' and trade_status= ' 2 ' GROUP by Merchant_code have machinesncreatedate>=4) as Tmptable ;

Problem: Walk a full table scan
Select count (DISTINCT merchant_code) Number of merchants from Pos_order where DATE (create_date) >= ' 2017-05-12 ' and DATE (Create_ Date) <= ' 2017-05-18 ' and trade_status= ' 2 ';

650) this.width=650; "title=" 2.png "src=" https://s3.51cto.com/wyfs02/M00/99/51/wKioL1lHRnCxOFOmAAAsCmhUAFo621.png "alt=" Wkiol1lhrncxofomaaascmhuafo621.png "/>select count (DISTINCT machine_sn) The number of trading tools from Pos_order where DATE ( create_date) >= ' 2017-05-12 ' and date (create_date) <= ' 2017-05-18 ' and trade_status= ' 2 ';

Found:
1 4 queries are pos_order queries.
2 unnecessary self-connections
3 count (secondary index) faster than COUNT (*)
4 count (distinct) optimization                 --forget it, that's not necessary.
Example:
Select count (DISTINCT machine_sn) The number of trading tools from Pos_order where DATE (create_date) >= ' 2017-05-12 ' and DATE ( create_date) <= ' 2017-05-18 ' and trade_status= ' 2 ';
Overwrite:
Explain select count (MACHINE_SN)   Number of trade tools   from Pos_order where Machine_sn=any (select DISTINCT MACHINE_SN from Pos_order)   and date (create_date) >= ' 2017-05-12 ' and date (create_date) <= ' 2017-05-18 ' and trade_status= ' 2 ';

650) this.width=650; "title=" 3.png "src=" https://s1.51cto.com/wyfs02/M00/99/51/wKioL1lHRvzjAW_yAABEu-bCbk4132.png "alt=" wkiol1lhrvzjaw_yaabeu-bcbk4132.png "/>any keyword, as long as the inner query statement is satisfied to return any one of the results, you can use the condition to execute the outer query statement.
All in contrast to any, the outer query statement can be executed only if all the results returned by the Inner query statement are satisfied.
Explain select COUNT (MACHINE_SN) The number of trading tools from Pos_order where Machine_sn=all (select distinct machine_sn from Pos_order);
MACHINE_SN | varchar (32) | NO | MUL | NULL ' Machine KSN number '

650) this.width=650; "title=" 4.png "src=" https://s4.51cto.com/wyfs02/M01/99/52/wKiom1lHRymyXI6HAAA38YNx4os641.png "alt=" Wkiom1lhrymyxi6haaa38ynx4os641.png "/>

The second type of syntax overrides:
Select count (DISTINCT machine_sn) Trade tool count from Pos_order where DATE (create_date) >= ' 2017-05-12 ' and DATE (create_date) & Lt;= ' 2017-05-18 ' and trade_status= ' 2 ';

Forced to walk the index, but there is a judgment or full table. Use the union ALL method.
Explain select count (DISTINCT machine_sn) The number of trading tools from the Pos_order union ALL Select COUNT (MACHINE_SN) from Pos_order Dex (Idx_create_date) where date (create_date) >= ' 2017-05-12 ' and date (create_date) <= ' 2017-05-18 ' and Trade_ status= ' 2 ';

650) this.width=650; "title=" 5.png "src=" https://s1.51cto.com/wyfs02/M02/99/52/wKiom1lHR1uRGNK8AABNaoloZkk172.png "alt=" Wkiom1lhr1urgnk8aabnaolozkk172.png "/>

Overwrite function name
Select count (DISTINCT machine_sn) Trade tool count from Pos_order where create_date between Date_sub (Curdate (), INTERVAL 1 day) and D Ate_sub (Curdate (), INTERVAL 7 day) and trade_status= ' 2 ';

650) this.width=650; "title=" 6.png "src=" https://s3.51cto.com/wyfs02/M01/99/52/wKioL1lHR6rg3yrOAAA-SDhHunU529.png "alt=" Wkiol1lhr6rg3yroaaa-sdhhunu529.png "/>

Very puzzled, how can not count.
Business SQL statement:
mysql> Select count (DISTINCT merchant_code) Number of merchants from Pos_order where DATE (create_date) >= ' 2017-05-12 ' and DATE (create_date) <= ' 2017-05-18 ' and trade_status= ' 2 ';
+--------------------+
| Number of Merchants        |
+--------------------+
|              39882 |
+--------------------+
1 row in Set (1 min 48.89 sec)

Mysql> Select Date_sub (curdate (), INTERVAL 7 day), Date_sub (Curdate (), INTERVAL 1 day);       
+------------------------------------+------------------------------------+
| date_sub ( Curdate (), INTERVAL 7 day) | Date_sub (Curdate (), INTERVAL 1 day) |
+------------------------------------+------------------------------------+
| 2017-05-12                           | 2017-05-18                          |
+------------------------------------+------------------------------------+
1 row in Set (0.00 sec)

Big put in front.
Mysql> Select count (DISTINCT machine_sn) Number of trading tools from Pos_order where Create_date >= date_sub (Curdate (), INTERVAL 1 da Y) and Create_date <= date_sub (Curdate (), INTERVAL 7 day) and trade_status= ' 2 ';
+--------------------+
| Number of trading Tools |
+--------------------+
| 0 |
+--------------------+
1 row in Set (0.00 sec)

The value is not right, but also no one.

650) this.width=650; "title=" 7.png "src=" https://s3.51cto.com/wyfs02/M01/99/52/wKioL1lHR9jj4CKyAADx9KsTuBk688.png "alt=" Wkiol1lhr9jj4ckyaadx9kstubk688.png "/>

Remove the function. Changed from all to range but the data is not equivalent.
Select count (DISTINCT merchant_code) Number of merchants from Pos_order where create_date>= ' 2017-05-12 00:00:00 ' and create_date& lt;= ' 2017-05-18 23:59:59 ' and trade_status= ' 2 ';

650) this.width=650; "title=" 8.png "src=" https://s3.51cto.com/wyfs02/M02/99/52/wKioL1lHSBmiLoJxAAAnFZdn2XM368.png "alt=" Wkiol1lhsbmilojxaaanfzdn2xm368.png "/>650" this.width=650; "title=" 9.png "src=" https://s4.51cto.com/ Wyfs02/m00/99/52/wkiom1lhsd2gsa89aabowcysczi024.png "alt=" Wkiom1lhsd2gsa89aabowcysczi024.png "/>

Select count (DISTINCT merchant_code) Number of merchants from Pos_order Force index (idx_create_date) where create_date>= ' 2017-05-12 00:00:00 ' and create_date<= ' 2017-05-18 23:59:59 ' and trade_status= ' 2 ';

The most effective way to optimize the distinct is to use the index to perform the row weight operation, first to find out the records of the row weight in passing count statistics, so that the effect is higher
Table magnitude: 14745537 1474W + data.
Current: The index of the Pos_order table is as follows:
Knowledge Point ha: If the time type create_date is used to do the operation, it is not indexed (therefore Idx_create_date index is invalid)

650) this.width=650; "title=" 10.png "src=" https://s2.51cto.com/wyfs02/M01/99/52/ Wkiol1lhsjlww4traaa9zmfzoyq393.png "alt=" Wkiol1lhsjlww4traaa9zmfzoyq393.png "/>650" this.width=650; "title=" 11. PNG "src=" Https://s1.51cto.com/wyfs02/M02/99/52/wKiom1lHSLXQ5d4EAABEQ-rrY3w025.png "alt=" Wkiom1lhslxq5d4eaabeq-rry3w025.png "/>

Table structure: Also excellent existence of the self-increment ID primary key.

650) this.width=650; "title=" 12.png "src=" https://s5.51cto.com/wyfs02/M02/99/52/ Wkiol1lhsocrgu9laaaqp3rx7t4738.png "alt=" Wkiol1lhsocrgu9laaaqp3rx7t4738.png "/>

This article from the "Clear Sky" blog, declined reprint!

Iposdb Database Date Function optimization